AGGR()

[Anonymous]

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?

[florentina_doga · Accepted solution]

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

[Anil_Babu_Samineni]

John, I recommend read this

Aggregations and Function Classes

Nice Explanation about AGGR.

[marcus_malinow]

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