Date comparisson in set modifier

[janvandernagel]

Hello

I'm trying to count the record with a date smaller then today.

With a hard coded date it is working:
=Count ({$ < NextDate={"<05-02-2020"}>} Maintenance)

I've tried to replace the date with the function Today(), that did not work.

So I've made a variable (Let varToday = floor(Today()); ) and want to use this variable:

=Count ({$ < NextDate={'<=$(varToday)'}>} Maintenance)

What am I doing wrong?

Thanks in advance

Jan

[Vegar]

It's hard to be sure about your issues, it depends on your system date format, but I'll try to guide you in the right direction. 

1. When you want to evaluate a modifier you should use " " not ' '. 

2. All single value set modifier evaluations are text based, your expression should return a string value.

{<Date = {'42769'}>}  will not work if your Date is formatted as a dual with a text representation like this '2020-02-03' or similar. You will need to generate {<Date = {'2020-02-03'}>} .

[janvandernagel · Accepted solution]

Thanks for putting me in the right direction. The date format was indeed the problem.
I've been puzzling for quite a while with this article (and your input):

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

Based on that I set a variable in the script:

Set vTodayTxt = "=Date(Today(),'DD-MM-YYYY')";

And I used that variable in the expression:

=Count ({$ <NextDate ={"<$(vTodayTxt)"}>} Maintenance)

[Vegar]

Glad it worked out for you!