Date Filter with Today(1) not working.

[danelooman]

I am trying to set up a bookmark from a certain date to current date. The problem is =today(1) seems to not work. I have dates in the original format of 2/21/2022 12:00:00 AM then I run Date() on it.  So I ran if today(1) = max([Calendar Date]) which happens to be today and it returns -1 so Qlik says they should match. 

I can get it to work with =Floor([Calendar Date])=Floor(Today(1)) so it makes me think there is some conversion not working. 

This is the sheet I am doing the testing on: 

When I click on the filter pane and try to filter to =today(1) it wants to select all of them. 

Just for anyone curious I am going to be passing this into the api so I am interested purely in the filter syntax for a certain date to current date. 

[danelooman · Accepted solution]

We managed to get it to work with =[Calendar Date]<=today(1)-1 and [Calendar Date]>='01/01/2022' . I am curious if anyone can tell me why we have to specify the field instead of just writing <=today(1)-1>='01/01/2022' or if anyone has a better way of doing this. 

[marcus_sommer]

Date() is a formatting and therefore your timestamp remained a timestamp regardless how it looked. Should it be a real date you need date(floor(DateField)). Usually you will need both - the timestamp + a date and linked to it multiple period-fields from a master-calendar.

- Marcus

[danelooman]

So I tried that. In theory then =today(1) or even =floor(today(1)) in a filter pane should work but instead they just select all. Any ideas on why?

[marcus_sommer]

You need to compare the today() against your date-field, like:

= DateField=today()

- Marcus

[danelooman]

I see. If you look at the first response I posted that is what we ended up doing and it worked even without adding the floor to the field. Thank you for helping me trouble shoot. I guess I just don't understand why on a filter pane I can put =01/01/2021 and it works but if I try =today it does not and requires the field comparison. Guess its just a qlik syntax quirk. 

[marcus_sommer]

Just providing a date like 01/01/2021 triggers a string-comparison of the search-value against the field-values. Using an expression like = today() returns a numeric value (each date is a number) and each value which isn't 0 is TRUE and therefore it means all field-values are TRUE. 

By  applying = DateField=today() only one field-value could be TRUE all other must be FALSE respectively from a numeric point of view it would be -1 and 0.

- Marcus