Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I have a problem regarding the above funktion. I have a table with a ordered timeseries and two interesting fields, "payout" and "terminated", where payout is a number and terminated is boolean. There is also a benchmark which is a formula just calculating on the timeseries.
In the end I want to get one value, the average of ("aggregated terminated payout"/"total payout")/"benchmark" per moment with the tag "terminated".
I got the formula in a table and everythings fine:
if(terminated={'Ja'} ,
(RangeSum(above(total aggr(sum({1<terminated= {'Ja'}>}payout),timeseries),0, rowno(total)))
/
sum(total{1}payout))
/
benchmark )
my problem is, that I cant get average of this formula in a KPI field, it says that "above funktion is not allowed inside aggregation".
Has someone an idea how to get this fixed?
Best,
Matthias
Is it possible to get those results with other formulas?
May be this
Avg(Aggr(
If(terminated={'Ja'}, (RangeSum(Above(TOTAL Aggr(Sum({1<terminated= {'Ja'}>}payout), timeseries), 0, RowNo(TOTAL)))/
Sum(TOTAL {1} payout))/benchmark)
, timeseries))
This doesnt work either..
What is the dimension of the chart were you are using this expression?
If(terminated={'Ja'}, (RangeSum(Above(TOTAL Aggr(Sum({1<terminated= {'Ja'}>}payout), timeseries), 0, RowNo(TOTAL)))/
Sum(TOTAL {1} payout))/benchmark)
the dimension is timeseries.
Take this as Input:
Timeseries | payout | terminated | Benchmark |
---|---|---|---|
1 | 10 | Ja | 0,1 |
1 | 15 | Nein | 0,1 |
2 | 5 | Nein | 0,2 |
3 | 20 | Nein | 0,3 |
3 | 1 | Ja | 0,3 |
And the Table I receive is this (without benchmark here, but it should be clear):
Timeseries | RangeSum(Above(TOTAL Aggr(Sum({1<terminated= {'Ja'}>}payout), timeseries), 0, RowNo(TOTAL) | If(terminated={'Ja'}, (RangeSum(Above(TOTAL Aggr(Sum({1<terminated= {'Ja'}>}payout), timeseries), 0, RowNo(TOTAL)))/ Sum(TOTAL {1} payout)) |
---|---|---|
Total | =max | =avg |
1 | 10 | =10/41 |
2 | 10 | - |
3 | 11 | =11/41 |
I hope this helps.
What I want is the field in the upper right, column average.
May be this
=Avg(Aggr(If(SubStringCount(Concat(DISTINCT '|' & terminated & '|'), '|Ja|') = 1,
(RangeSum(Above(TOTAL Aggr(Sum({1<terminated= {'Ja'}>}payout), Timeseries), 0, RowNo(TOTAL)))/
Sum(TOTAL {1} payout))), Timeseries))
I get some result, but I don't know what it means. Can you please explain this function a little? Also, since I'm using Qliksense I cant open your file (better said I can open it, but I cant see any dashboards)
Hi Sunny,
I have an additional Question regarding this Formula: I have another Dimension called Country. When I use your formula in a KPI field and set a Filter for the Country it works perfect. Now I wanted to display it in a Table with the Dimension Country. The aggregated Value is correct, but the individual Values per Country are wrong. When I select one country its correct again.
Could you help me here to display it in a Table? I tried to do it myself, but I didnt manage to do it..
try to add chart dimensions to your Aggr function as below:
=Avg(Aggr(If(SubStringCount(Concat(DISTINCT '|' & terminated & '|'), '|Ja|') = 1,
(RangeSum(Above(TOTAL Aggr(Sum({1<terminated= {'Ja'}>}payout), Timeseries), 0, RowNo(TOTAL)))/
Sum(TOTAL {1} payout))), Country, Timeseries))
If you have only the Country dimension.
Not entirely sure, if this will work, but I think the Matthias might have to remove TOTAL keyword and divided by TOTAL at country level....
=Avg(Aggr(If(SubStringCount(Concat(DISTINCT '|' & terminated & '|'), '|Ja|') = 1,
(RangeSum(Above(TOTAL Aggr(Sum({1<terminated= {'Ja'}>}payout), Timeseries), 0, RowNo(TOTAL)))/
Sum(TOTAL <Country> {1} payout))), Country, Timeseries))