# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for
Did you mean:  Contributor III

## Above function in aggregation

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

1 Solution

Accepted Solutions  MVP

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))

20 Replies  Partner

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

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

you mean a Qlik Sense KPI object ?  Contributor III
Author

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

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

you can't do this inside a kpi object.  Contributor III
Author

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 ))  Partner

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  Partner

maybe this:

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

(

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

/

sum(total{1}payout)

)

/

benchmark

) Tags
Community Browser