Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
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.