Average calculations

[tmumaw]

Hi everyone,

Could someone please explain to me why my averages at different levels would be different. Should the sum of the months, divided by 3 equal the quarter and so on?

sum(aggr(avg(EmpCt_ZEMPCT),[Profit Center],FISYR,FISPD))

I get the following for months:

Jan    7,780

Feb   7,898

Mar   8,285

I get the following for QTR:

sum(aggr(avg(EmpCt_ZEMPCT),[Profit Center],FISYR,QTR))

QTR 1    8,140

sum(aggr(avg(EmpCt_ZEMPCT),[Profit Center],FISYR))

Year:

8,365

[Anil_Babu_Samineni]

Ma be TOTAL Keyword for Average, If not may be needful bit clarification

[tmumaw]

Nope

[RSvebeck]

Do you have zero, null or negative values among those you do avg() around?

Robert

[tmumaw]

Nope

[RSvebeck]

If you do sum()/count() instead of avg(), do you get same result?

[RSvebeck]

I try to avoid avg()

read this

https://community.qlik.com/blogs/qlikviewdesignblog/2013/07/29/averages

[tmumaw]

Robert,

Take a look at the attached file.  I have employee counts (EmpCt_ZEMPCT) for every week.  Along with that I have employees which have been terminated during the same time period (FISYR, QTR, FISPD and WKNO).  You can see why I used the aggr(Avg)..  What is weird it works at the Profit Center level, but as I roll it up to higher levels it doesn't.

Thanks

[RSvebeck]

Sure, I can have a look, but you still should avoid avg() almost always, it is slower and as Henric Cronström writes: 

Avg() function returns the average value, but – this is usually not the value that you want. 

The Avg() function returns the average transactional value, whereas you probably are looking for a larger amount.

Use sum()/count(), it is what you are after normally.

Robert

[RSvebeck · Accepted solution]

ok Ihad a look, and difference is explained by the fact that avg() is average on profit center level all the time, even when you go on higer level, and then you get an sort of avgerage sum of averages instead of just average on higher levels.

Avoid this problem by using sum()/count()

That will always work, regardless of level, and it is faster for qlik to calculate.

Robert