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

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

 T X Y X*Y Cum(X) Cum(X*Y) Capt Avg Cum(X*Y) / Cum(X) 1 2 2 4 2 4 2 2 2 3 6 4 10 2,5 3 2 4 8 6 18 3 4 2 5 10 8 28 3,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:

 T X Y X*Y Cum(X) Cum(X*Y) Capt Avg Cum(X*Y) / Cum(X) 4 2 5 10 8 28 3,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

• ###### Re: a running calculated average, using expressions outside selection

Hi.

You need to use a specific model to do that, http://community.qlik.com/message/229267#229267

• ###### Re: a running calculated average, using expressions outside selection

Found a solution which I'd like to share, because it opened a new world for me :-)

This is the formula I use now in my example:

( RangeSum(Above(sum(X*Y),0,RowNo())) + sum( total {<T={"<\$(=min(T))"}>} aggr( X * Y,T)) )
/
(
RangeSum(Above(sum(X),0,RowNo())) + sum( total {<T={"<\$(=min(T))"}>} aggr( X ,T)) )

The trick is in the combination of sum,total,set analysis and aggr what gives me what I wanted, run expressions like (X*Y) over a field T ouside of the current selection of T.

In short this will give you always the same result, regardless of selection of T:

sum ( total {<T={"<5"}>} aggr(only( {1} Y * X ),T ))

Hope this helps someone!

• ###### Re: a running calculated average, using expressions outside selection

If user filter on other field than "T" does it work?

• ###### Re: a running calculated average, using expressions outside selection

sum ( total {<X={"<999"}>} aggr(only( {1} Y * X ),T ))   does not has the same result

When filtering/selecting it doesn't sum all rows anymore, only the selected.