topic Re: AGGR() in QlikView

AGGR()

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to understand the AGGR Function. Can someone show me how the AGGR is calcuated based of the example numbers below? Visually I need to undertstand how this is calculated. For example Region MID differs from 20.8 to 52.5. I know it's based off the Program but how is that calulcated?

Re: AGGR()

florentina_doga — Fri, 09 Sep 2016 13:52:39 GMT

for region=mid

column 1

sum(all)=125 count(id)=6 125/6=20.83

column2

sum for region=mid =125 count(id)=6 125/6=20.83

column3

sum for region=mid and program=high=5 count(id)=5 25/5=5

sum for region=mid and program=low=100 count(id)=1 100/1=100 and (5+100) / 2 (2 diferent aggregatin)=52.5

Re: AGGR()

Anil_Babu_Samineni — Fri, 09 Sep 2016 14:03:51 GMT

John, I recommend read this

Aggregations and Function Classes

Nice Explanation about AGGR.

Re: AGGR()

marcus_malinow — Fri, 09 Sep 2016 14:07:37 GMT

Hi John,

In a nutshell, what AGGR does is apply a level of aggregation prior to your final aggregation. You can think of this as building a temporary table prior to your final aggregation. The columns in this table are the second and following parameters in the AGGR function.

So, in your example above, avg(Days) is the same as avg(aggr(avg(Days), region))) as your table includes region anyway, thus making the aggr redundant.

In your last column you are using AGGR to aggregate avg(Days) across region and Program. If you build a chart with region and Program and avg(days) you can see what the data looks like. You then average this measure up to the region level.

I'd be a bit cautious with this particular calculation as the result is that all Programs will be given equal weight. Averages of averages aren't generally a good thing.

Hope this helps.

Marcus