I need to create a way to quickly filter dates 1 year before now, 6 months before now, 1 month before now, 1 week before now, etc. Buttons looks to be the best option. Looked into Date Picker, but to select the range manually takes too long and had some other issues. I created a button and tested it with "Select values matching search criteria" and "Select values in a field" actions. It worked with both actions when value was "='2024-09-23 00:00:00.000000'", worked with "Select value in a field" when value was "='2024-09-23'". Then tried a value suggested by chatGPT "='>=' & Date(AddMonths(Today(), -6))". None of the actions worked. Tried similar things like "='>=2024-01-01'", "='>2024-01-01'", etc, but nothing worked. For me it looks that value field has to have all the values I want to select, but I could not find a way to create a list of dates for the past 6 months dinamically. I was thinking about setting variables as well. What could I do in this situation?
1 Answer
I think you need to give the format of the date as well. In my case the date field is in format YYYY-MM-DD so my button expression is:
='<=' & Date(AddMonths(Today(),-6) ,'YYYY-MM-DD')
And this works ok with Select values matching search criteria action.
3 Comments
Laurynas G
Finally! It was wrong format, but it would be nice to hear another way to solve it. At the moment I was getting the date from Oracle database like this: "TO_DATE('1971-12-31', 'YYYY-MM-DD') + pr2.created_date as created_date". It looks that it was returning format like this "2024-09-23 00:00:00.000000". I Googled how to change this format to 'YYYY-MM-DD' and the only way I found was to use to_char, but then it was stopping to be a date. What could I do? Current way (='>=' & Date(AddMonths(Today(),-6) ,'YYYY-MM-DD 00:00:00.000000')) doesn't look very nice
Stefan Stoychev
Personally I would always "re-date" these fields when loading the data. To be sure that ,at least in Qlik, the fields are
date (and because these are dual fields they will be also number). To achieve this you can extract the fields as they are (or use to_char) and then during data load in Qlik make it whatever format you want but as date. For example: if the DB field returns '2025-02-05 00:00:00.000000 ill cast that to Date(Date#(some_field, 'YYYY-MM-DD 00:00:00.000000'), 'YYYY-MM-DD'). This way the field in Qlik will be in YYYY-MM-DD format.Laurynas G
Awesome, thanks!
