Above function in aggregation

[Anonymous]

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

[Anonymous]

Hi @Sunny, thank you very much for beeing a great help again!

I adjusted it a little and this way it works perfect:

=Avg(Aggr(If(SubStringCount(Concat(DISTINCT '|' & terminated & '|'), '|Ja|') = 1,

(RangeSum(Above(TOTAL Aggr(Sum({1<terminated= {'Ja'}>}payout), Country, Timeseries), 0, RowNo(TOTAL)))/

Sum(TOTAL <Country> {1} payout))), Country, Timeseries))

While playing around, it seems like the TOTAL inside the RowNo() Function could stay, but it works both ways.

Thanks!