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

[sunny_talwar · Accepted solution]

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

Best,

Sunny

[kwdaniels]

Thanks, Sunny--that was quick! This does solve the stated problem, though I think the AsOfTable approach will be superior if it allows us to be more flexible with drill-down dimensions instead of being limited to a hard-coded "Resource Business Unit" dimension.

I will look at your AsOfTable solution as soon as I get a chance.

[kwdaniels]

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.

[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

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

[kwdaniels]

Thanks--it looks like that solution has the advantage of guaranteeing that the most recent available date will be chosen from the AsOfTable, even if the data isn't reloaded on a given day.

[sunny_talwar]

If you are talking about my response, then no, if the app is not refreshed on a given day, then my proposed solution with show yesterday's data. My assumption was that the app will refresh each day and that's how the most recent data is going to be pulled in. If that assumption is incorrect, then it might make sense to stick with the solution that you proposed above .