
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- « Previous Replies
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
in which object that expression works and where it doesn't ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It does work in a table with the dimension "timeseries", but not in a KPI field.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you mean a Qlik Sense KPI object ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
yes. In general the above funktion doesnt seem to work with an avg funktion around it...


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
could you show me the expression you are using with avg and aggr ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you can't do this inside a kpi object.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
maybe this:
=avg( {< terminated={'Ja'} >}
(
RangeSum(above(total aggr(sum({1<terminated= {'Ja'}>}payout),timeseries),0, rowno(total)))
/
sum(total{1}payout)
)
/
benchmark
)

- « Previous Replies
- Next Replies »