29 Replies Latest reply: Dec 21, 2016 11:56 AM by Gysbert Wassenaar RSS

    growth rates in Qlik Sense

    Jes Lee

      Hello,

       

      I would like to create a new variable in Qlik Sense for growth rates.   As an example of what I'm looking for: 

       

       

      [Alabama Jobs] is the main field that I'm pulling from an external database-- I would like to compute the period over period growth rate as in column three.   There there a way to hard code this in the data editor ?   

         

      DateAlabama Jobs Period over period growth rate
      10.2015159
      11.201520026%
      12.2015130-35%
      1.201625092%
      2.2016140-44%
      3.2016110-21%
      4.2016100-9%
      5.20161000%
      6.20161000%
      7.201618080%
      8.201634089%
      9.2016249-27%
      10.2016200-20%
      11.201624221%
        • Re: growth rates in Qlik Sense
          Gysbert Wassenaar

          Something like this:

           

          Temp:

          LOAD

               Date(Date#(Date,'M.YYYY'),'M.YYYY') as Date

               [Alabama Jobs]

          FROM

               the_source_data

               ;

           

          Result:

          LOAD

               Date,

               [Alabama Jobs],

               Num([Alabama Jobs] / Previous([Alabama Jobs]) - 1, '#%;-#%') as [Period over period growth rate]

          RESIDENT

               Temp

          ORDER BY

               Date

               ;

           

          DROP TABLE Temp;

            • Re: growth rates in Qlik Sense
              Jes Lee

              What if I wanted to set the growth rate to a year on a monthly occurring series? 

                • Re: growth rates in Qlik Sense
                  Gysbert Wassenaar

                  Then first aggregate the numbers at those levels. But that's something that's usually done in the charts, not the script. Doing those kind of aggregations in the charts gives more flexibility as selections will work nicely with that. Fixing the numbers in the script is less flexible. It's normally only done if necessary for performance reasons.

                    • Re: growth rates in Qlik Sense
                      Jes Lee

                      Are you referring to creating a measure on the front end?  Could you provide an example of how I would aggregate to get year over year growth rates? 

                        • Re: growth rates in Qlik Sense
                          Gysbert Wassenaar

                          Yeah, creating a measure. I'm going to assume you have a field called Year and you're using that as dimension in your chart or table. You can use Sum([Alabama Jobs]) to sum up those amounts so you get totals for each year. If you use a simple table then you can use the Above() function to refer to the value of the row above. So Above(Sum([Alabama Jobs])) would return the sum for the previous year. You can then create a measure that divides the two values to calculate the year-on-year variation: Sum([Alabama Jobs]) / Above(Sum([Alabama Jobs])) - 1.

                          If you sort the Year dimension in descending order you'd need to use the Below() function instead of Above().

                        • Re: growth rates in Qlik Sense
                          Jes Lee

                          So just to clarify what I'm trying to achieve, I'm looking t o compute the growth rate for each month over the same month a year ago. 

                           

                          So,   Oct2016/Oct2015 - 1

                            • Re: growth rates in Qlik Sense
                              Gysbert Wassenaar

                              Ok, that's going to be more complicated. The best way to deal with this kind of problem is to add a so-called As-Of table to your data model. Before we start with that you need to view a video and read two blog posts:

                              A Beginners' Introduction to Set Analysis (video)

                              A Primer on Set Analysis

                              The As-Of Table

                               

                              The As-Of Table will be used to create a new field that will be used as dimension in the charts and in the set analysis expression that we'll need for the solution to your question. That new field will link to the real Month field so we can select the Month values we're interested in and calculate the value for the months we want to compare, e.g. Oct2016 with Oct2015. We'll need some set analysis expressions to calculate those results.

                               

                              View the video and read the blog posts and let me know if you don't understand how those things will help us create the solution to your problem.

                                • Re: growth rates in Qlik Sense
                                  Jes Lee

                                  Hi Gysbert,

                                   

                                  I loaded the Asof Table referencing the Master Calendar.   I get an error message stating the field <Year-Month> is not found. 

                                   

                                  Year and Month are well defined in the Master Calendar so I'm not certain why it's not working. 

                                   

                                   

                                  Thank you,

                                  Jessica

                                   

                                  QuartersMap:

                                  MAPPING LOAD

                                  rowno() AS Month,

                                  'Q' & Ceil (rowno()/3) AS Quarter

                                  AUTOGENERATE (12);

                                   

                                   

                                  Temp:

                                  LOAD

                                  min(DATE) AS minDate,

                                  max(DATE) AS maxDate

                                  Resident BLS_data;

                                   

                                   

                                  Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

                                  Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

                                  DROP Table Temp;

                                   

                                  TempCalendar:

                                  LOAD

                                  $(varMinDate) + Iterno()-1 As Num,

                                  DATE($(varMinDate) + IterNo()-1) AS TempDate

                                  AutoGenerate 1 While ($(varMinDate) + IterNo() -1) <= $(varMaxDate);

                                   

                                  MasterCalendar:

                                  LOAD

                                      TempDate AS DATE,

                                      Week(TempDate) AS Week,

                                      Year(TempDate) AS Year,

                                      Month(TempDate) AS Month,

                                      Year(TempDate)*100+Month(TempDate) AS MonthYear,

                                      Day(TempDate) AS Day,

                                      ApplyMap('QuartersMap', month(TempDate), Null()) AS Quarter,

                                      Year(TempDate)&ApplyMap('QuartersMap', month(TempDate), Null()) AS QuarterYear,

                                      Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) AS WeekYear,

                                      WeekDay(TempDate) AS WeekDay

                                  Resident TempCalendar

                                  Order By TempDate ASC;

                                  Drop Table TempCalendar;

                                   

                                   

                                  //////////////////////////////////////////////////////////////////  CREATION OF AS-OF TABLE ////////////////////////////////////////////////////

                                   

                                  // ======== Create a list of distinct Months ========

                                  tmpAsOfCalendar:

                                  Load distinct

                                    [Year-Month]

                                  Resident MasterCalendar

                                    ;

                                   

                                  // ======== Cartesian product with itself ========

                                  Join (tmpAsOfCalendar)

                                   

                                   

                                  Load

                                    [Year-Month] as AsOfMonth

                                  Resident

                                    tmpAsOfCalendar

                                      ;

                                   

                                  // ======== Reload, filter and calculate additional fields ========

                                  [As-Of Calendar]:

                                  Load

                                    [Year-Month],

                                    AsOfMonth,

                                    Round((AsOfMonth-[Year-Month])*12/365.2425) as MonthDiff,

                                      Year(AsOfMonth)-Year([Year-Month]) as YearDiff

                                  Resident

                                    tmpAsOfCalendar

                                  Where

                                    AsOfMonth >= [Year-Month]

                                      ;

                                    

                                  Drop Table tmpAsOfCalendar;

                                    • Re: growth rates in Qlik Sense
                                      Gysbert Wassenaar

                                      Oh, ok [Year-Month] is not a construction of the two fields Year and Month. It's a field by itself and it needs to be created first before it can be used in the AsOf calendar.

                                       

                                      Your script creates a field MonthYear:

                                      Year(TempDate)*100+Month(TempDate) AS MonthYear,

                                      You're also creating it as a field that's not a date which will cause problems later if used for calculating the MonthDiff and YearDiff fields which expect dates in the AsOfMonth and [Year-Month] fields. So replace the line that creates MonthYear with:

                                      Date(TempDate,'YYYY-MM') AS [Year-Month],

                                      You can change the date format YYYY-MM to something else if you like. You don't need MonthYear afaict, but if you want to keep it then don't replace that line but add the other one below it so the [Year-Month] field will be created as well.

                                  • Re: growth rates in Qlik Sense
                                    Gysbert Wassenaar

                                    Once you've done the homework I gave you and understand the basic idea behind those concepts have a look at the attached qlik sense app. On the second sheet you'll find a pivot table object that uses the above() function and a table object that makes use of the As-Of table and the set analysis expressions. In the Data load editor you can find the piece of script code that creates the As-Of table.

                            • Re: growth rates in Qlik Sense
                              Jes Lee

                              I see.  I've attached the QVF ----the measure is already created titled State jobs_yy% change.

                               

                              a. (Sum({$<MonthYear={'Sep2016'}>}BLS_jobs_state) / Sum({$<MonthYear={'Sep2015'}>}BLS_jobs_state))-1

                               

                               

                              Thanks.

                              • Re: growth rates in Qlik Sense
                                Jes Lee

                                Here you go.  This file is a bit smaller.

                                • Re: growth rates in Qlik Sense
                                  Jes Lee

                                  Hi Gysbert,

                                   

                                  I followed all of the steps, and it does output the dates.  I'm not able to attach the QVF, because it's too large and it takes 4hrs to load the data, but I'm hoping a screenshot would be sufficient. 

                                   

                                   

                                  Here are the fields I've created: 

                                   

                                  (1) BLS_jobs_state last 12 mos:    Sum({<MonthDiff={12}>}BLS_jobs_state)

                                  (2) BLS_jobs_state_curr month:     Sum({<MonthDiff={0}>}BLS_jobs_state)

                                  (3) BLS_jobs_state, yy%:              Sum({<MonthDiff={0}>}BLS_jobs_state)/Sum({<MonthDiff={12}>}BLS_jobs_state) - 1

                                   

                                  I'm not sure what I'm doing incorrectly.  I made sure to select one state, which is a dimension for this series, and at least two years. 

                                   

                                   

                                  Thank you,

                                  Jessica