Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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')
)
Hi,
Try this expression.
=Rangesum(sum(Products)/Sum(ListCount), Before(ProductsPerSession))
Regards,
Kaushik Solanki
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
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
Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations
Hi Gysbert,
Thanks for sending this. Very useful indeed! I will give it a go with different options and let you know.
Thanks,
Karthik
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
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')
)
This worked brilliantly gysbert!! Thank you very much.