nested aggregation to calculate weighted average

Thu, 22 May 2014 20:44:17 GMT

Suppose I have a table of values like this:

year value

2011 1

2011 0

2011 1

2012 0

2012 1

2013 0

2013 1

That is, 4 values for 2011, 2 values for 2012, and 5 values for 2013.

I would like to implement the weighted average: (2011*4 + 2012*2 + 2013*5 ) / (4+2+5). Or in the general case, that would be weighted average = sum( Ni * Xi /sum(Ni)) where i is in {2011, 2012, 2013} in this example. I think it is a nested aggregate that I want, and I don't know if it is possible or how best to implement it (I'm a newbie).

I want the end user to select years 2011 and 2012 (say) from displayed list, and the weighted average to be calculated for data corresponding to just those 2 years (i in {2011, 2012} only).

I was trying something like this: $(=sum(count(value)*year / $(=sum(count(value)) ) )

Any suggestions would be appreciated!

Re: nested aggregation to calculate weighted average

preminqlik — Thu, 22 May 2014 21:26:04 GMT

hi there , please find attachment

Re: nested aggregation to calculate weighted average

Fri, 23 May 2014 02:18:53 GMT

I think I figured it out:

=(sum(year*aggr(count(value), year)))/count(value)

Finally realized that aggr is a lot like "GROUP BY" in SQL.

topic nested aggregation to calculate weighted average in QlikView

nested aggregation to calculate weighted average

Re: nested aggregation to calculate weighted average

Re: nested aggregation to calculate weighted average