# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

Regards.

Tags (1)
1 Solution

Accepted Solutions
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:

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

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

Result:

3 Replies
MVP

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

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

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:

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

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

Result:

New Contributor III