Qlik Community

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 
Search instead for 
Did you mean: 
mwalter_yopeso
Contributor III
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
sunny_talwar

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

View solution in original post

20 Replies
agigliotti
Partner
Partner

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

mwalter_yopeso
Contributor III
Contributor III
Author

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

agigliotti
Partner
Partner

you mean a Qlik Sense KPI object ?

mwalter_yopeso
Contributor III
Contributor III
Author

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

agigliotti
Partner
Partner

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

OmarBenSalem
Partner
Partner

you can't do this inside a kpi object.

mwalter_yopeso
Contributor III
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 ))

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

agigliotti
Partner
Partner

maybe this:

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

(

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

/

sum(total{1}payout)

)

/

benchmark

)