Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to calculate YoY sales and MoM sales ,my resource data is like this:
customer date product sales
A 20150301 bike 1000
.......
How can I calculate the YOY and MOM sales,
I mean that, when I select the condition year(date) which includes 2013,2014,2015, the YOY sales shows the sum sales of 2012,2013,2014.
so may be the way of Sum({$<year(date)={$(=Max(year(date))-1)}>}sales) can not work
so what should I do to get the YOY and MOM?
Thanks a lot
Hi,
while you can formulate the corresponding condition within your Set Analysis, you'd be much-much better off if you calculated these conditions as flags in your calendar. Or, at least calculate the field Year in your dataset.
However, to answer your specific question, you need to formulate your set analysis filter in the following format:
Field = {Value}
or
Field = {"simple search condition"}
or
Field = {"=advanced search condition"}
With that in mind, you can define your condition with a value using the field Year (if you calculate it):
Year = {$(=Max(year(date))-1)}
or as an advanced search condition for the field Date:
date = {"=year(date)=$(=Max(year(date))-1)"}
You can learn these techniques and a lot more in my new book QlikView Your Business.
cheers,
Oleg Troyansky
QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense
Thanks to Oleg Troyansky
Do you mean that,when I use sum({"=year(date)=$(=Max(year(date))-1)"} sales)
I can get the correct result?
I mean that when I select three years ( 2012,2013,2014 ),I can get the sum result of (2011,2012,2013)
No and no, unfortunately.
1. The correct syntax is :
sum({<date={"=year(date)=$(=Max(year(date))-1)"}>} sales)
2. Using this syntax, you can only get one year - the one preceding the Max year. In your example, 2013. In order to get a range of years, you need to formulate your expression as a range:
sum({<date={"=year(date)>=$(=Min(year(date))-1) and year(date)<=$(=Max(year(date))-1)"}>} sales)
I haven't checked this syntax, but something like this should work.
cheers,
Oleg Troyansky