topic Re: Average calculations in QlikView

Average calculations

tmumaw — Wed, 17 May 2017 15:41:50 GMT

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

Re: Average calculations

Anil_Babu_Samineni — Wed, 17 May 2017 15:57:01 GMT

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

Re: Average calculations

tmumaw — Wed, 17 May 2017 17:15:56 GMT

Nope

Re: Average calculations

RSvebeck — Wed, 17 May 2017 18:11:16 GMT

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

Robert

Re: Average calculations

tmumaw — Wed, 17 May 2017 18:15:33 GMT

Nope

Re: Average calculations

RSvebeck — Wed, 17 May 2017 18:20:07 GMT

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

Re: Average calculations

RSvebeck — Wed, 17 May 2017 18:25:09 GMT

I try to avoid avg()

read this

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

Re: Average calculations

tmumaw — Wed, 17 May 2017 18:47:17 GMT

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

Re: Average calculations

RSvebeck — Wed, 17 May 2017 18:52:31 GMT

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

Re: Average calculations

RSvebeck — Wed, 17 May 2017 19:08:31 GMT

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

Re: Average calculations

tmumaw — Thu, 18 May 2017 15:09:54 GMT

Thanks for your help Robert. I think I just needed to go home and sleep on it. I changed everything per your suggestion and it worked.