6 Replies Latest reply: May 5, 2015 1:40 PM by Jonathan Poole RSS

    Calculating LY Sales Metrics by Month or Date

      Hello,

       

      I am attempting to create a LY Sales metric so I can graph Sales and LY Sales by Month. My data source has sales by month, where the "Date" field contains the first day of the month (ex. 2/1/2014, 3/1/2014, 4,1/2014, ect)

       

      I have added the following expression for this calculation, but can not get it to return values.

       

      LY Sales = SUM({$<Date={$(=addyears(Date,-1))}>}Sales)

       

      Can someone help me understand what I am doing incorrectly here? Or if there is a better way to approach this issue?

        • Re: Calculating LY Sales Metrics by Month or Date
          Jonathan Dienst

          Hi

           

          This sort of analysis is much easier if you have a calendar that includes Month and Year as separate fields. During the LOAD...

           

               LOAD ...

                    Year(Date) As Year,

                    Month(Date) As Month,

                    ....

           

          For your chart, use Month as the dimension, and create two expressions:

           

          • Current year  =Sum({<Year = {"$(=Max(Year))"}>} Sales)
          • Previous year   =Sum({<Year = {"$(=Max(Year) - 1)"}>} Sales)

           

          HTH

          Jonathan

            • Re: Calculating LY Sales Metrics by Month or Date

              Jonathan,

               

              That is not getting me to my desired solution. I want to display the LY sales by month along side the sales for the current month as follows:

               

              Ex. Jan-2016 sales are displayed and the LY sales column displays Jan-2015 sales

               

                 

              DateSalesLY Sales
              1/1/2015130001000
              2/1/2015140002000
              3/1/2015150003000
              4/1/2015160004000
              5/1/2015170005000
              6/1/2015180006000
              7/1/2015190007000
              8/1/2015200008000
              9/1/2015210009000
              10/1/20152200010000
              11/1/20152300011000
              12/1/20152400012000
              1/1/20162500013000
              2/1/20162600014000
              3/1/20162700015000
              4/1/20162800016000
              5/1/20162900017000
              6/1/20163000018000
              7/1/20163100019000
              8/1/20163200020000
              9/1/20163300021000
              10/1/20163400022000
              11/1/20163500023000
                • Re: Calculating LY Sales Metrics by Month or Date
                  Michael Tarallo

                  Hi Courtney,

                   

                  Thanks for your question.

                   

                  Is the table you provided in the last post the actual source data, or is that your desired result?

                   

                  Would a Pivot Table chart object do what you need, such as this:

                   

                   

                  As Jonathan mentioned you can lightly transform the data - using the data load editor - or by creating new dimensions in the master items list. If you create a Year and Month field from your date field it will make the task much easier.

                   

                  LOAD

                      "Date",

                      Year(Date) as Year,

                      Month(Date) as Month,

                      Sales,

                      "LY Sales"

                  FROM [lib://ThreadData]

                  (html, codepage is 1252, embedded labels, table is @1);

                   

                   

                  Also please check out these New to Qlik Sense Videos - id you have not to learn more about Qlik Sense.

                   

                  See attached .qvf file - you can copy to your C:\Users\<user profile>\Documents\Qlik\Sense\Apps and refresh desktop with F5.

                   

                  Note - the data sources is using this actual community thread and sucking in the data from the data table you provided.

                   

                  Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                   

                  Regards,

                  Mike Tarallo

                  Qlik