topic Rolling average for 3 months in a pivot table in App Development

Rolling average for 3 months in a pivot table

infock12 — Mon, 12 Dec 2016 10:09:22 GMT

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.

Re: Rolling average for 3 months in a pivot table

kaushiknsolanki — Mon, 12 Dec 2016 10:19:55 GMT

Hi,

Try this expression.

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

Regards,

Kaushik Solanki

Re: Rolling average for 3 months in a pivot table

infock12 — Mon, 12 Dec 2016 11:05:50 GMT

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

Re: Rolling average for 3 months in a pivot table

infock12 — Mon, 12 Dec 2016 11:56:42 GMT

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

Re: Rolling average for 3 months in a pivot table

Gysbert_Wassenaar — Mon, 12 Dec 2016 12:02:47 GMT

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

Re: Rolling average for 3 months in a pivot table

infock12 — Mon, 12 Dec 2016 13:34:48 GMT

Hi Gysbert,

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

Thanks,

Karthik

Re: Rolling average for 3 months in a pivot table

infock12 — Tue, 13 Dec 2016 17:41:14 GMT

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

Re: Rolling average for 3 months in a pivot table

Gysbert_Wassenaar — Wed, 14 Dec 2016 07:30:35 GMT

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

)

Re: Rolling average for 3 months in a pivot table

infock12 — Wed, 14 Dec 2016 11:12:17 GMT

This worked brilliantly gysbert!! Thank you very much.