Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
Why is there no 9 in best 5? and two 8s?
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:
Thanks for your help