
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Another Date Comparison problem
I have a set of data that is refreshed weekly. I need to isolate the last weeks data from previous weeks for some expressions. I create a variable vCurrent_Roster_Date which is derived from the Load Script. My Current set of data is from 09/23/2024 so that is the value of that variable. The Floor(vCurrent_Roster_Date) value is 45558.
I've tried a myriad of combinations of expression to count the number of Names in the latest extract based on a Filter Pane selection. I started with if statements like:
=if(floor(Roster_Date) = Floor(vCurrent_Roster_Date), count(NAME))
Even though the expression editor says this is OK, this gives me an invalid dimension error.
So I tried to create a set expression.
Count({$<Floor(Roster_Date) = {<=(Floor(vCurrent_Roster_Date))}>} NAME )
That's just one way I tried to do it. I've tried using $ and "" "" and all sorts of other permutations based on the posts in:
Dates in Set Analysis - Qlik Community - 1472511
A Primer on Set Analysis - Qlik Community - 1468344
and I'm just not getting it. I keep getting a set modifier expression error no matter what I try.
I've tried both of these methods using various ways of representing the dates also.
// =num(Date#(vCurrent_Roster_Date, 'MM/DD/YYYY')) - Yields 45538 type value
// =(num(Max(Date(Roster_Date, 'MM/DD/YYYY')))) - Yields 45538 type value
// =(Max(Date#(Roster_Date, 'MM/DD/YYYY'))) - Yields 09/23/2024 type value
// =(Max(Date(Roster_Date, 'MM/DD/YYYY'))) - Yields 09/23/2024 type value
// =vCurrentRosterDate - Yields 09/23/2024 type value
// =Date(vCurrent_Roster_Date) - Yields 09/23/2024 type value
// =Date#(vCurrent_Roster_Date) - Yields 09/23/2024 type value
// =Date#(vCurrent_Roster_Date, 'MM/DD/YYYY') - Yields 09/23/2024 type value
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Within set analysis, the field must just be a field, not a function.
So you will need to floor the Roster_Date in the load script to get it to match other floored date field values.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Within set analysis, the field must just be a field, not a function.
So you will need to floor the Roster_Date in the load script to get it to match other floored date field values.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I ended up biting the bullet and created a Master Calendar for the first time. Not as painful as I thought it would be using the script from Creating A Master Calendar - Qlik Community - 341286.
My expression is now:
=Count({<[Roster_Date.autoCalendar.Date] = {"$(vCurrent_Roster_Date)"} >} NAME)
Thanks for the help.
Ron
