Average of last 5 better results from the 6 last results

[hhajjali]

Hello,

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

ID	RESULTS
1	5
2	8
3	7
4	5
5	8
6	9
7	5
8	4
9	10
10	2

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.

[sunny_talwar]

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

[OmarBenSalem · Accepted solution]

with this:

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

U'd have this:

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

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

Result:

[hhajjali]

Thanks for your help