Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

infock12
Contributor

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

Re: Rolling average for 3 months in a pivot table

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

)

8 Replies

Re: Rolling average for 3 months in a pivot table

Hi,

Try this expression.

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

Regards,

Kaushik Solanki

infock12
Contributor

Re: Rolling average for 3 months in a pivot table

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
Contributor

Re: Rolling average for 3 months in a pivot table

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

infock12
Contributor

Re: Rolling average for 3 months in a pivot table

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
Contributor

Re: Rolling average for 3 months in a pivot table

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

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

)

infock12
Contributor

Re: Rolling average for 3 months in a pivot table

This worked brilliantly gysbert!! Thank you very much.