How to compare a measure against previous day's value

[kwdaniels]

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

[Anonymous]

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

[kwdaniels]

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):

[sunny_talwar]

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

[sunny_talwar]

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

Above(TOTAL Sum([Resource HeadCount]))

[kwdaniels]

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!

[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

[kwdaniels]

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?

[sunny_talwar]

This is partly working solution which doesn't completely work because of the use of 365 days here. In leap years we have to look 366 days.... Working on the As Of Table solution in the mean time you look at this

Aggr(Above(If(DayNumberOfYear([Date]) = DayNumberOfYear(Today()), Sum([Resource HeadCount])), 365), [Resource Business Unit], Date)

[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)