4 Replies Latest reply: Jul 17, 2012 5:25 AM by Dirk Ooms RSS

    a running calculated average, using expressions outside selection

      Dear community,

      I have currently a table like this where I calculate a 'Captive Avg', based on the cumulative(X*Y) expression, devided by the cum(X) value.


      TXYX*YCum(X)Cum(X*Y)Capt Avg Cum(X*Y) / Cum(X)



      These cum values are calculated like this:



      What I need now is that if the user makes a selection on the Time (T) field, my Capt Avg field still shows the same value.

      Desired Result with T selection on 4:


      TXYX*YCum(X)Cum(X*Y)Capt Avg Cum(X*Y) / Cum(X)


      When I select T = 4, I need a formula that returns 3,5. I already have a formula which returns the correct cum(X) value when a selection is made:

      =aggr(RangeSum(Above(sum( {1} X),0,RowNo())),T)


      But the problem is I would need something like this

      =aggr(RangeSum(Above(sum( {1}  (X*Y) ),0,RowNo())),T)

      but the {1} can only be used on fields, not on functions like X*Y.

      I also tried with things like this

      =RangeSum(Above(aggr( (X*Y), T),0,RowNo())) 

      but an aggregation is always limited to the selection as far as I see.



      (Pre-calculating in script is not an option, this is a simplified example of the problem)


      Best Regards,

      Dirk Ooms