Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
Hi.
You need to use a specific model to do that, http://community.qlik.com/message/229267#229267
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!
If user filter on other field than "T" does it work?
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.