Aggregate of previous n values

[Qlik_ULG]

Hi,

I am trying to get an average of the previous 5 values of my dimension.

So, I have a Rating score associated with a date and to a work_request_ID field.

I am trying to get an average Rating of the 5 most recent Rating values.

Is there a way of doing this in an expression?

I am currently using this expression, but it is taking all values, not just the previous 5 I need.

RangeSum(above(Sum(Rating),0,4))

/5

Thanks in advance

[Frank_Hartmann]

Maybe this:

Rangesum(above(aggr(Sum(Rating),work_request_ID),0,5))/5

or

Rangeavg(above(aggr(Sum(Rating),work_request_ID),0,5))

[anders_thorngaa]

Hi Shane,

Your formula should work (it works for me) - see attached file.

Notice: Formula should be simplified: RangeAvg(Above(Sum(Sales),0,5))

Regards

Anders

[Qlik_ULG]

thanks for the reply Frank, but no this is not working for me unfortunately.

The Rating is only associated with a specific date and unique work_request but I do not want to have the work_request included in the calculation as I need the average Rating of the 5 most recently created work_requests

[Qlik_ULG]

Thanks for the reply Anders,

it is not working for me.

The previous Ratings I have are;

4

4

4

2

3

2

So when I sum the first 5 records, I get 17, divided by 5 is 3.4.

Qlikview is giving me 3.8.

Is there a sorting issue maybe and it is not selecting the 5 records that I need? It should be the 5 most recent values.

[Frank_Hartmann]

can u share a sample?

[Qlik_ULG]

!Hi Frank,

attached is a data sheet of the the data. Only the rows that are shaded I wish to have included in the average calculation.

[Frank_Hartmann · Accepted solution]

try this in a TextBox:

=sum({$<Date={"=rank(num(Date)) <=5"}>} Rating)/5

[Qlik_ULG]

that is exactly it.

Thanks a million Frank! (an old Irish saying!)