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)
      1224242
      22364102,5
      32486183
      425108283,5

       

       

      These cum values are calculated like this:

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

       

      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)
      425108283,5

       

      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