Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_ULG
Creator
Creator

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
Master II
Master II

try this in a TextBox:

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

View solution in original post

8 Replies
Frank_Hartmann
Master II
Master II

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

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Master II
Master II

can u share a sample?

Qlik_ULG
Creator
Creator
Author

!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
Master II
Master II

try this in a TextBox:

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

Qlik_ULG
Creator
Creator
Author

that is exactly it.

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