How do we sum values on last day of each time period in Qlik Sense?

[kwdaniels]

See attached Qlik Sense app. I would like to sum the headcount values in a table as of the last date of an arbitrary period, whether end of week, end of month, end of quarter, end of year, etc.

The included Raw Data table lists sample active employee names for each effective date, along with a Headcount value of 1 for each employee. (In real life, the full employee roster is repeated for every day starting from the company's inception, but in this sample app, I'm including only a few days' worth of data, for 1/1/16, 1/2/16, 1/31/16, 2/1/16, and 2/29/16).

If no date filters are applied, I want to see the count of all employees as of the latest date in the data table, which is 2/29/2016. This should give me a count of 6, since 6 employees are listed on 2/29/2016. If I apply a date filter for the month 2016-01, it should give me a count of 4, since 4 employees are listed on the last day of that period (1/3/2016).

I first thought I could define a measure like the following, but Qlik didn't like the presence of the "Max" function embedded within the "Sum" function:

sum(if(EffectiveDate=Max(EffectiveDate),Headcount))

So then I tried rearranging the syntax as follows, but it did not give me any results:

if(EffectiveDate=Max(EffectiveDate),sum(Headcount))

Then I tried using Set Analysis as follows, but instead of giving me the total value for the last date in the data (6 employees on 2/29/16), it unexepectedly yielded a total for all dates (26), as if no filer were being applied:

Sum({$<[EffectiveDate]={"=$(=Date((Max([EffectiveDate])),'M/D/YYYY'))"}>} [Headcount])

Then I decided to try a Month End approach (though I would prefer a more generic "Max Date" approach that I was attempting above). See the table "Problem with MonthEnd Function?" in the attached app.

You can see that the MonthStart function works as expected, as it properly identifies the start date of each month in the "Month Start Headcount" column and filters the headcount accordingly.

However, MonthEnd is not working as expected, as you can see from the "Bad Month End Headcount" column, which I've defined exactly like the "Month Start Headcount" function, except that I've replaced MonthStart with MonthEnd. Note that it is not showing totals for 1/31/16 or 2/29/16 as I would have expected. If I hard code the month end dates as in "Good Month End Headcount", it works as expected, but that's not a workable production solution.

Is this a bug with MonthEnd, or am I missing something?

This seems like it should be a simple task, but I've spent several hours trying to find a way to obtain generic last-day-of-period totals to no avail. Could someone recommend an approach that will work for any arbitrary time period without having to hard-code anything?

[kwdaniels]

Update: I discovered a set analysis expression that provides the desired result.

In my original post, I mentioned that the following equality ("=") expression did not perform any filtering:

Sum({$<[EffectiveDate]={"=$(=Date((Max([EffectiveDate])),'M/D/YYYY'))"}>} [Headcount])

I found it works to apply a "between" filter, looking for a date that's less than or equal to the max effective date and that's greater than the day before the max effective date:

Sum({$<[EffectiveDate]={"<=$(=Date((Max([EffectiveDate])),'M/D/YYYY'))"}, [EffectiveDate]={">$(=Date((Max([EffectiveDate]) - 1),'M/D/YYYY'))"} >} [Headcount])

This yields a headcount of 6 when no date filters are applied (effective 2/29/16) and a headcount of 4 when the month 2016-01 is selected (effective 1/31/16).

I'm not sure why the equality approach above did not work, but the "between" expression does work for me.

Though I now have what I need, it remains a mystery why the MonthEnd function didn't work as expected as described in my original post. Is it a bug in Qlik Sense or user error?