Show values of the past

[jorditorras]

Dear Gurus,

I have a table like this:

Date-Time	R Factor
4 October 18:38:01	20
4 October 18:37:01	12
4 October 18:36:01	45
4 October 18:35:01	52
4 October 18:34:01	21
4 October 18:33:01	34
4 October 18:32:01	53
4 October 18:31:01	35
4 October 18:30:01	45
4 October 18:29:01	33
4 October 18:28:01	25
4 October 18:27:01	12

Where the first row is the last record loaded. I have to calculate an Average between 9 and 11 values ago. That would mean an Average between 33, 25, 12.

Does anybody know a way to reach this calculation?

Thanks in advance.

Regards,

Jordi

[sunny_talwar · Accepted solution]

Is this in a KPI object? May be this

RangeAvg(

FirstSortedValue([R Factor], -[Date-Time], 9),

FirstSortedValue([R Factor], -[Date-Time], 10),

FirstSortedValue([R Factor], -[Date-Time], 11))

[jorditorras]

Genius!!!!!!

[qliksus]

One more way would be as below

RangeAvg(Below(sum(amt),9,11))

[sunny_talwar]

How will RangeAvg() work in a KPI object? Did you forget to add Aggr() may be? Although one other way is to use set analysis....

[qliksus]

OP didn't mentioned anything about KPI and yes it wont work for KPI until we use the advanced aggregation method to sort the values

[sunny_talwar]

I guess I deduced that from the request that he is looking for the average of 9th, 10th and 11th highest number... this didn't seemed like a rolling average request.... but I you are right, OP did not mention that he needed this in the KPI. My bad and I apologize

[qliksus]

No need for apology dude