Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
tmumaw
Valued Contributor

Average calculations

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
RSvebeck
Valued Contributor

Re: Average calculations

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

Svebeck Consulting AB

View solution in original post

10 Replies
Highlighted

Re: Average calculations

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
tmumaw
Valued Contributor

Re: Average calculations

Nope

Highlighted
RSvebeck
Valued Contributor

Re: Average calculations

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


Robert

Svebeck Consulting AB
Highlighted
tmumaw
Valued Contributor

Re: Average calculations

Nope

Highlighted
RSvebeck
Valued Contributor

Re: Average calculations

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

Svebeck Consulting AB
Highlighted
RSvebeck
Valued Contributor

Re: Average calculations

I try to avoid avg()


read this


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



Svebeck Consulting AB
Highlighted
tmumaw
Valued Contributor

Re: Average calculations

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

Highlighted
RSvebeck
Valued Contributor

Re: Average calculations

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

Svebeck Consulting AB
Highlighted
RSvebeck
Valued Contributor

Re: Average calculations

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

Svebeck Consulting AB

View solution in original post