Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
infock12
Creator III
Creator III

Rolling average for 3 months in a pivot table

Hi all,

I've looked into various solutions here for this sort of queries earlier, but perhaps because of the fields I am using, I may not be able to crack it. You will be able to understand when you look at the qvw. file attached.


I have the data from Apr to Sept this year. What I am trying to get is the rolling average: For April, I would like the expression to take the April value because no values before that. For May, I would like it to take avg( April + May) values. For June, I would like it to take avg(April + May + June). For July, I would like it to take the previous 3 months, i.e., avg(July, June and May), etc., so on and so forth for the following months.


In background colour, I would like the same as well. If the values don't meet the target, then Red, otherwise Green.


Any help is appreciated. I will keep trying too.






1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try this expression:

=If(ColumnNo()=1, if(sum(Products)/Sum(ListCount)>=sum(Target),  'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_s_r.png'),

If(Before(RangeAvg(sum(Products)/Sum(ListCount), before(sum(Products)/Sum(ListCount),0,3)))<RangeAvg(sum(Products)/Sum(ListCount), before(sum(Products)/Sum(ListCount),0,3)), 'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_s_r.png')

)


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Try this expression.

=Rangesum(sum(Products)/Sum(ListCount), Before(ProductsPerSession))

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
infock12
Creator III
Creator III
Author

Thanks Kaushik,

Tried this but it keeps adding for the previous months. For example, if values for Apr, May and June are 2.5, 2.0, 2.3 respectively, then it gives a value of 2.5, 4.5 and 6.8.

However, I would like the average for the previous 3 months, so the value of June in this case should be (6.8)/3, which is 2.27.

Thanks,

Karthik

infock12
Creator III
Creator III
Author

Hi Kaushik,

I tried the below and it seems to be working. However, the values seem be to slightly off from the excel values. Not sure why. I have pasted both the files again with the workings.

=RangeAvg(sum(Products)/Sum(ListCount), before(sum(Products)/Sum(ListCount),0,3))

I have attached both the excel sheet and qvw. I have pivoted in excel and showed the values in yellow to say how it compared to the qlikview pivot table. (Please note I have left the original table on the top in qvw that has the original expression and added another pivot with the above expression.

Thanks,

Karthik

Gysbert_Wassenaar

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations


talk is cheap, supply exceeds demand
infock12
Creator III
Creator III
Author

Hi Gysbert,

Thanks for sending this. Very useful indeed! I will give it a go with different options and let you know.

Thanks,

Karthik

infock12
Creator III
Creator III
Author

Hi Gysbert and Kaushik,

As I mentioned in my original e-mail, I would like to RAG rate the above table. I have got everything pretty much right, but just one final bit is missing. I am saying for every column (which is the month), look at the previous column( previous month) and if it is higher, then make it red, otherwise green. It works but for the first month, everything is showing in Red. I wanted the first column to take 'Target' as the benchmark and if the values are above Target, green arrow, otherwise Red arrow. You will understand when you see the attached file. Please let me know your thoughts.

Thanks,

karthik

Gysbert_Wassenaar

Try this expression:

=If(ColumnNo()=1, if(sum(Products)/Sum(ListCount)>=sum(Target),  'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_s_r.png'),

If(Before(RangeAvg(sum(Products)/Sum(ListCount), before(sum(Products)/Sum(ListCount),0,3)))<RangeAvg(sum(Products)/Sum(ListCount), before(sum(Products)/Sum(ListCount),0,3)), 'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_s_r.png')

)


talk is cheap, supply exceeds demand
infock12
Creator III
Creator III
Author

This worked brilliantly gysbert!! Thank you very much.