# 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
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