2 Replies Latest reply: May 22, 2014 10:18 PM by trial teqqa RSS

    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!