Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
danelooman
Creator
Creator

Date Filter with Today(1) not working.

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: 

Screen Shot 2022-02-21 at 11.58.28 PM.png

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. 

Labels (1)
1 Solution

Accepted Solutions
danelooman
Creator
Creator
Author

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. 

View solution in original post

6 Replies
danelooman
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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