Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
hi there , please find attachment
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.