Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

20 Replies
Anonymous
Not applicable
Author

Is it possible to get those results with other formulas?

sunny_talwar

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

Anonymous
Not applicable
Author

This doesnt work either..

sunny_talwar

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)

Anonymous
Not applicable
Author

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.

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

Anonymous
Not applicable
Author

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)

Anonymous
Not applicable
Author

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

agigliotti
Partner - Champion
Partner - Champion

try to add chart dimensions to your Aggr function as below:

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

sunny_talwar

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