Another Date Comparison problem

[rmadursk]

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

[Lisa_P · Accepted solution]

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.

[rmadursk]

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