20 Replies Latest reply: Nov 24, 2017 8:58 AM by Matthias Walter

# 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

• ###### Re: Above function in aggregation

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

• ###### Re: Above function in aggregation

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

• ###### Re: Above function in aggregation

you mean a Qlik Sense KPI object ?

• ###### Re: Above function in aggregation

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

• ###### Re: Above function in aggregation

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

• ###### Re: Above function in aggregation

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

• ###### Re: Above function in aggregation

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

• ###### Re: Above function in aggregation

maybe this:

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

(

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

/

sum(total{1}payout)

)

/

benchmark

)

• ###### Re: Above function in aggregation

you can't do this inside a kpi object.

• ###### Re: Above function in aggregation

Is it possible to get those results with other formulas?

• ###### Re: Above function in aggregation

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

• ###### Re: Above function in aggregation

This doesnt work either..

• ###### Re: Above function in aggregation

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)

• ###### Re: Above function in aggregation

the dimension is timeseries.

Take this as Input:

TimeseriespayoutterminatedBenchmark
110Ja0,1
115Nein

0,1

25Nein0,2
320Nein0,3
31Ja0,3

And the Table I receive is this (without benchmark here, but it should be clear):

TimeseriesRangeSum(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
110=10/41
210-
311=11/41

I hope this helps.

What I want is the field in the upper right, column average.

• ###### Re: Above function in aggregation

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

• ###### Re: Above function in aggregation

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)

• ###### Re: Above function in aggregation

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

• ###### Re: Above function in aggregation

=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.

• ###### Re: Above function in aggregation

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

• ###### Re: Above function in aggregation

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!