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

[agigliotti]

in which object that expression works and where it doesn't ?

[Anonymous]

It does work in a table with the dimension "timeseries", but not in a KPI field.

[agigliotti]

you mean a Qlik Sense KPI object ?

[Anonymous]

yes. In general the above funktion doesnt seem to work with an avg funktion around it...

[agigliotti]

could you show me the expression you are using with avg and aggr ?

[OmarBenSalem]

you can't do this inside a kpi object.

[Anonymous]

I'm using this formula in the table:

if(terminated={'Ja'} ,

(RangeSum(above(total aggr(sum({1<terminated= {'Ja'}>}payout),timeseries),0, rowno(total)))

/

sum(total{1}payout))

/

benchmark )

and for the KPI I just want

avg(

if(terminated={'Ja'} ,

(RangeSum(above(total aggr(sum({1<terminated= {'Ja'}>}payout),timeseries),0, rowno(total)))

/

sum(total{1}payout))

/

benchmark ))

[OmarBenSalem]

you can't use above in the KPI object since above sees the 'line/value' above the current position; while there is no dimension in the KPI object

[agigliotti]

maybe this:

=avg( {< terminated={'Ja'} >}

(

RangeSum(above(total aggr(sum({1<terminated= {'Ja'}>}payout),timeseries),0, rowno(total)))

/

sum(total{1}payout)

)

/

benchmark

)