Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Qlik_ULG
New Contributor III

Aggregate of previous n values

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

1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor II

Re: Aggregate of previous n values

try this in a TextBox:

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

8 Replies
Frank_Hartmann
Honored Contributor II

Re: Aggregate of previous n values

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
New Contributor III

Re: Aggregate of previous n values

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
New Contributor III

Re: Aggregate of previous n values

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
New Contributor III

Re: Aggregate of previous n values

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
Honored Contributor II

Re: Aggregate of previous n values

can u share a sample?

Qlik_ULG
New Contributor III

Re: Aggregate of previous n values

!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
Honored Contributor II

Re: Aggregate of previous n values

try this in a TextBox:

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

Qlik_ULG
New Contributor III

Re: Aggregate of previous n values

that is exactly it.

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