topic Re: Date comparisson in set modifier in QlikView

Date comparisson in set modifier

janvandernagel — Wed, 05 Feb 2020 16:46:08 GMT

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

Re: Date comparisson in set modifier

Vegar — Wed, 05 Feb 2020 18:12:39 GMT

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'}>} .

Re: Date comparisson in set modifier

janvandernagel — Wed, 05 Feb 2020 21:28:21 GMT

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)

Re: Date comparisson in set modifier

Vegar — Wed, 12 Feb 2020 20:19:00 GMT

Glad it worked out for you!