3 Replies Latest reply: Sep 14, 2015 1:09 PM by Oleg Troyansky RSS

    How to calculate   year-on-year in Qlik sense

      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

        • Re: How to calculate   year-on-year in Qlik sense
          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

            • Re: How to calculate   year-on-year in 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)

                • Re: How to calculate   year-on-year in Qlik sense
                  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