Analytical functions > more details regarding "average"

Previous Member

Hi,

 

I would like to use one of the availableanalytical functions : average

According to the help,

Average: it returns the average value of the series (Total/Time Length);

Can anyone give more details on how the average calculation works ?

• how happens the aggregations ?
• Does the function apply after all aggregations and context selections of the screen and layout ?

 

I have tried an example of average calculation and get different result depending on using the analytical function or not:

 

1. V001 one cube by {Day, Country, Claim, Model} having the total costs
2. V004 one counter cube having the same dimensions (MXC)
   
3. the sparsity is:
4. dataview context: no selections, no pagers, nothing
5. layout:
   1. blocks a and d uses V001, block b uses V004.
   2. Country set by row
   3. Average analytical function on block d.
   4. nothing else special
6. the dataview looks like this:
7. i would have expected to see the same values for blocks c and d, e.g. for IT: 203 € and not 968 €

 

I might have not understood the scope of the average function within the analytical function.

Could someone help me understanding it better ?

 

Thanks in advance,

Björn Reuber

Hi,

 

Your comparing the result an a "wrong" level. The MXC is using a disitinct count (so without having a real mathematical prove it will count the distinct claims on country [?] level], while the average is only calculated for a time period. Furthermore without knowing the time period (should the average in block d be on daily, monthly, yearly base?). Its hard to tell the correct result.

 

Which average do you expect? Just add the time period for your analysis to "by Row" and use the total feature average to see the correct value.

 

regards

Björn

Previous Member

Ow i apologize, looks like i missed this sentence from the online help (chapter 13, §1.1 About Analytical Function):

Analytical function work across time entities only

 

I was looking for this kind of average calculation (across all dimensions of the cube except the Country dimension):

<span class="keyword token">SELECT</span> Country<span class="punctuation token">,</span> <span class="token function">AVG</span><span class="punctuation token">(</span>cost<span class="punctuation token">)</span> <span class="keyword token">FROM</span> Mytable <span class="keyword token">GROUP</span> <span class="keyword token">BY</span> Country<span class="line-numbers-rows"><span></span></span>

Now I understand this cannot be provided by the average analytical fonction.

Using the MXC cube gives the expected result.

Previous Member

a question can only be "answered" as final status on community.board.com.
In my case, the final status should be something like "question was meaningless" or anything in this direction. Does this status exist ? It would be wrong to set it to "answered" to my mind. But it would not be satisfying to let it without final status, since it is solved...

What do you suggest Björn Reuber ?

Björn Reuber

from my point of view solved is solved. Nevertheless if its solved by one or more correct answers or cause the reason of the request is not valid any more

 

regards

Björn