Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hhajjali
New Contributor III

Average of last 5 better results from the 6 last results

Hello,

Here is my problem, I have a table as follow:

ID RESULTS
15
28
37
45
58
69
75
84
910
102

I need to calculate the average of the last 5 better results from 6 last results. In my case a the smaller the result is the better.

So in the example above, the 6 last results are results with ID 5 to 10. The best 5 are IDs: 10, 8, 7, 6 and 5.

The average will be (2+4+5+9+8)/5 = 5.6.

ID is always incremental.

Thanks for your help.

Regards.

Tags (1)
1 Solution

Accepted Solutions
omarbensalem
Esteemed Contributor

Re: Average of last 5 better results from the 6 last results

with this:

if(rank(aggr(sum({<ID={">=$(=max( all RESULTS)-5)"}>}RESULTS),ID))>1,sum(RESULTS))


U'd have this:

Capture.PNG

For the Average, use a KPI object with this expression:

AVG({<ID={"=rank((sum({<ID={'>=$(=max( all RESULTS)-5)'}>}RESULTS)))>1"}>}RESULTS)


Result:

Capture.PNG

3 Replies
MVP
MVP

Re: Average of last 5 better results from the 6 last results

Why is there no 9 in best 5? and two 8s?

omarbensalem
Esteemed Contributor

Re: Average of last 5 better results from the 6 last results

with this:

if(rank(aggr(sum({<ID={">=$(=max( all RESULTS)-5)"}>}RESULTS),ID))>1,sum(RESULTS))


U'd have this:

Capture.PNG

For the Average, use a KPI object with this expression:

AVG({<ID={"=rank((sum({<ID={'>=$(=max( all RESULTS)-5)'}>}RESULTS)))>1"}>}RESULTS)


Result:

Capture.PNG

hhajjali
New Contributor III

Re: Average of last 5 better results from the 6 last results

Thanks for your help