How to calculate year-on-year in Qlik sense

[#1]

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

[Oleg_Troyansky]

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

[#3]

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)

[Oleg_Troyansky]

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