a running calculated average, using expressions outside selection

[#1]

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

[nstefaniuk]

Hi.

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

[#3 · Accepted solution]

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!

[nstefaniuk]

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

[#5]

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.