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

# How to compare a measure against previous day's value

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:

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))"}

Ken Daniels

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

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

Thanks for your suggestion, Brian.

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

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

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

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

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

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

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

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:

```if(DayNumberOfYear([Date]) = DayNumberOfYear(Today()), sum([Resource 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?

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

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)

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

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

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)

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

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.

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

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

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

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

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;

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

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.

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

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 .