Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

nested aggregation to calculate weighted average

Suppose I have a table of values like this:

year  value

2011 1

2011 0

2011 0

2011 1

2012 0

2012 1

2013 0

2013 0

2013 1

2013 1

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!

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: nested aggregation to calculate weighted average

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.

2 Replies
preminqlik
Valued Contributor II

Re: nested aggregation to calculate weighted average

hi there , please find attachment

Not applicable

Re: nested aggregation to calculate weighted average

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.

Community Browser