topic Re: Aggregate of previous n values in QlikView

Aggregate of previous n values

Qlik_ULG — Fri, 02 Nov 2018 11:56:00 GMT

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))

Thanks in advance

Re: Aggregate of previous n values

Frank_Hartmann — Fri, 02 Nov 2018 13:05:58 GMT

Maybe this:

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

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

Re: Aggregate of previous n values

anders_thorngaa — Fri, 02 Nov 2018 13:09:03 GMT

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

Re: Aggregate of previous n values

Qlik_ULG — Fri, 02 Nov 2018 13:13:41 GMT

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

Re: Aggregate of previous n values

Qlik_ULG — Fri, 02 Nov 2018 13:16:47 GMT

Thanks for the reply Anders,

it is not working for me.

The previous Ratings I have are;

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.

Re: Aggregate of previous n values

Frank_Hartmann — Fri, 02 Nov 2018 13:26:30 GMT

can u share a sample?

Re: Aggregate of previous n values

Qlik_ULG — Fri, 02 Nov 2018 13:39:50 GMT

!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.

Re: Aggregate of previous n values

Frank_Hartmann — Fri, 02 Nov 2018 14:20:59 GMT

try this in a TextBox:

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

Re: Aggregate of previous n values

Qlik_ULG — Fri, 02 Nov 2018 14:24:02 GMT

that is exactly it.

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