16 Replies Latest reply: Dec 7, 2016 4:54 PM by Sunny Talwar RSS

    How to compare a measure against previous day's value

    Ken Daniels

      We have a measure called [Resource HeadCount] that has a value of 1 for every employee for every day since the inception of our company.

       

      We would like to introduce a new measure that compares the headcount total for any given day with the headcount total for the day before.

       

      (In the real world, this will be the year before, but if I can make this work for the day before, I can make it work for the year before.)

       

      We also have a date dimension with a column called [Date].

       

      The following thread discusses a similar requirement that I attempted to adapt to our situation, but it does not work; it returns a value of 0 every time:  Previous Day - Set analysis - Not working | Qlik Community

       

      This was my first attempt that did not work; it returns 0 for all dates:

       

      Sum ({$< [Date] ={"$(=Date([Date]-1))"}>}  [Resource HeadCount])

       

      To provide further background, below is an expression that does return values, but only for the reported date; it doesn't allow me to compare the headcount for the reported date with that of the previous date:

       

      Sum ([Resource HeadCount])

       

      This expression also returns a value, but only for the hard-coded date specified (1/2/2006):

       

      Sum ({$< [Date] ={"$(=Date('1/2/2006','M/D/YYYY h:mm:ss[.fff] TT'))"}>} [Resource HeadCount])

       

      Here are some other expressions I attempted, all of which return a value of 0:

       

      Sum ({$< [Date] ={"$(=Date([Date],'M/D/YYYY h:mm:ss[.fff] TT')-1)"}>}  [Resource HeadCount])

      Sum ({$< [Date] ={"$(=Date([Date]-1,'M/D/YYYY h:mm:ss[.fff] TT'))"}>}  [Resource HeadCount])

      Sum ({$< [Date] ={"$(=Date([Date],'M/D/YYYY h:mm:ss[.fff] TT'))"}>} [Resource HeadCount])

       

      The following expression returns a value, but only for the most recent date; we need to to make a general measure that provides the headcount for the previous day for ALL dates:

       

      sum({$< Date={"$(=Date(Max(Date) - 1,'M/D/YYYY h:mm:ss[.fff] TT'))"}>} [Resource HeadCount])

       

      I am curious to know how the correct answer at Previous Day - Set analysis - Not working | Qlik Community could have worked. It seems that the the "At Date" value would never equal the day before the "At Date" value:

       

      [As at Date] ={"$(=Date([As at Date]-1))"}

       

      Thanks in advance for your help!

       

      Ken Daniels

        • Re: How to compare a measure against previous day's value
          Brian MacDonald

          Have you looked at the 'Above' function?  If the dates are in chronological order the previous days value will be given by Above(ResourceHeadCount).

            • Re: How to compare a measure against previous day's value
              Ken Daniels

              Thanks for your suggestion, Brian.

               

              I tried the following expression, but doesn't return anything:

              Above(Sum([Resource HeadCount]))

              FWIW, our date dimension table has an entry for every day from 1/1/2016 through 12/31/2017. In addition to the [Date] column, the table includes a number of other columns like [Day Number in Epoch] (sequential number from 1 through max date), [Calendar Year], [Calendar Quarter], [Billing Year], [Billing Quarter], etc.

               

              Here's what the data looks like when using the "Above" function (see column HCPrevDay):

               

              headcount.png

                • Re: How to compare a measure against previous day's value
                  Sunny Talwar

                  Since you have more than one dimension, try adding a TOTAL keyword within Above() function

                   

                  Above(TOTAL Sum([Resource HeadCount]))

                    • Re: How to compare a measure against previous day's value
                      Ken Daniels

                      Thanks, Sunny! I was able to meet the immediate objective of a rolling Year-Over-Year headcount using this expression:

                       

                      if(DayNumberOfYear([Date]) = DayNumberOfYear(Today()), Above(TOTAL Sum([Resource HeadCount]), 365))

                       

                      To get the YOY headcount change percentage, I used this expression:

                       

                      if(DayNumberOfYear([Date]) = DayNumberOfYear(Today()), (Sum([Resource HeadCount]) - Above(TOTAL Sum([Resource HeadCount]), 365))/Above(TOTAL Sum([Resource HeadCount]), 365))

                       

                      The main limitation to this approach is that if I add it to a line chart and then add another dimension (office), the values are far off base. It works correctly at the entire company level, but not when drilling into offices. I wonder if that's because of the use of the TOTAL option.

                       

                      I haven't yet had a chance to try your suggestion to use the As-Of Table, but it looks interesting. Perhaps it will solve the office drilling problem.

                       

                      I'll provide an update once I've solved the office drilling issue.

                       

                      Thanks again!

                        • Re: How to compare a measure against previous day's value
                          Sunny Talwar

                          I would definitely suggest you to go in that direction. AsOfTable is a very interesting and efficient way to handle comparison of dates and doing that sort of thing. With regards to your current situation, may be if you can share a sample, we might be able to help you better here

                            • Re: How to compare a measure against previous day's value
                              Ken Daniels

                              Thanks, Sunny.

                               

                              I studied the As-Of Table option but decided it wasn't a fit for this particular need, though we'll keep it in mind for rolling accumulations in the future. The reason it's not ideal at this point is that we're needing to report as of a specific day of the year rather than rolling up period-to-date measures. The Above function works superbly until I add another dimension.

                               

                              Since you asked for a sample, I'm attaching a Qlik Sense QVF and am including a screenshot that illustrates the issue.

                               

                              The measures in both of the tables in the screenshot below are defined as follows:

                               

                              Headcount:

                               

                              if(DayNumberOfYear([Date]) = DayNumberOfYear(Today()), sum([Resource HeadCount]))
                              
                              

                               

                              Prev Year Headcount:

                               

                              if(DayNumberOfYear([Date]) = DayNumberOfYear(Today()), Above(TOTAL Sum([Resource HeadCount]), 365))

                               

                              The first table below, which is for the entire company and is not segmented by business unit, is correct. Note that it reports 92 employees on 12/6/2016 and 79 employees for the previous year.


                              But the second table below reports incorrect "Prev Year Headcount" values. For 12/6/2016, it should show 67 for the "CONS" business  unit and 12 for the "CORP" business unit, but instead, it shows 14 and 70, respectively.


                              The only difference between the two tables is that the second table includes an additional dimension, "Resource Business Unit".  The "Above" function with the TOTAL qualifier is not grouping the counts by Resource Business Unit as I was hoping. I tried adding <Resource Business Unit> immediately after the TOTAL qualifier, but apparently the Above function does not support that syntax.

                               

                              How can I properly tally the headcounts by the Resource Business Unit dimension?

                              headcount by business unit.png

                    • Re: How to compare a measure against previous day's value
                      Sunny Talwar

                      I think for year before, you might be better of using The As-Of Table

                      • Re: How to compare a measure against previous day's value
                        Sunny Talwar

                        Actually I really want to use AsOfTable, but since I don't have source data, I can only give you a front end solution.

                         

                        Aggr(Above(If(DayNumberOfYear([Date]) = DayNumberOfYear(Today()), Sum([Resource HeadCount])), RangeSum(Date, -SetDateYear(Date, Year(Date)-1))), [Resource Business Unit], Date)

                         

                        Capture.PNG

                        • Re: How to compare a measure against previous day's value
                          Sunny Talwar

                          And finally the AsOfTable approach using random data. Make sure to look at the AsOfTable Relation tab.

                           

                          Best,

                          Sunny

                            • Re: How to compare a measure against previous day's value
                              Ken Daniels

                              Sunny,

                               

                              Very nice solution! As I see it, a significant advantage of the AsOfTable solution is that we don't have to hard-code the dimension name (e.g., "Resouce Business Unit") as in the AGGR front end solution. This allows us to add or remove dimensions as needed and use drill-down dimensions without having to modify the underlying measure.

                               

                              I've modified your "Comparison Chart" table to include an additional dimension called "Resource Office", which is a child of "Business Unit" in the company hierarchy. The comparison chart table correctly handles this, as you can see in the attached solution.

                               

                              In addition, I added an alternate column called "Previous Year (Simplified)". It provides the same headcount values as your "Previous Year" measure when I run it for today's date (12/7/2016) after reloading the data but is simpler for me to read. To make it work, I added a new column called AsOfDayNumberOfYear in the AsOfDate table.

                               

                              Original "Previous Year" measure:

                               

                              Sum({<AsOfMonth = {$(=Max(AsOfMonth))}, AsOfDay = {"$(=Max({<AsOfMonth = {$(=Max(AsOfMonth))}, AsOfYear = {$(=Max(AsOfYear))}>} AsOfDay))"}, Flag = {'Previous Year'}>}HeadCount)

                               

                              My "Previous Year (Simplified)" measure:

                               

                              Sum({<AsOfDayNumberOfYear = {$(=DayNumberOfYear(Today()))}, Flag = {'Previous Year'}>}HeadCount)

                               

                              I'm marking your latest solution as the Correct Answer but am also attaching my revisions to prove the flexibility of your solution when adding new dimensions.

                                • Re: How to compare a measure against previous day's value
                                  Sunny Talwar

                                  Thanks for sharing your final solution with us. I think it was a great idea to introduce AsOfDayNumberOfYear in your expression as it certainly improves your expression. I guess I did not know that your were going to look at only Today's data vs last few years of data.... But I am glad I was able to motivate you enough to learn about AsOfTable

                                   

                                  Best,

                                  Sunny

                                  • Re: How to compare a measure against previous day's value
                                    Sunny Talwar

                                    Further simplification of the expression

                                     

                                    Sum({<Flag2 = {1}, Flag = {'Previous Year'}>}HeadCount)

                                     

                                    Where I created Flag2 like this:

                                     

                                    AsOfTable:

                                    LOAD Date as AsOfDate,

                                      Year(Date) as AsOfYear,

                                        Num(Month(Date)) as AsOfMonth,

                                        Day(Date) as AsOfDay,

                                      SetDateYear(Date, Year(Date)-1) as Date,

                                       If(SetDateYear(Date, Year(Today())) = SetDateYear(Today(), Year(Today())), 1, 0) as Flag2,

                                      'Previous Year' as Flag,

                                        DayNumberOfYear(Date) as AsOfDayNumberOfYear

                                    Resident Table;

                                     

                                    Concatenate (AsOfTable)

                                    LOAD Date as AsOfDate,

                                      Year(Date) as AsOfYear,

                                        Num(Month(Date)) as AsOfMonth,

                                        Day(Date) as AsOfDay,

                                      Date,

                                       If(SetDateYear(Date, Year(Today())) = SetDateYear(Today(), Year(Today())), 1, 0) as Flag2,

                                      'Current Year' as Flag,

                                        DayNumberOfYear(Date) as AsOfDayNumberOfYear   

                                    Resident Table;