Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
rmadursk
Contributor III

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

Labels (4)
1 Solution

Accepted Solutions
Lisa_P
Employee

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.

View solution in original post

2 Replies
Lisa_P
Employee

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
Contributor III
Author

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