Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have date field
Load
Datefield
Where Date(Datefield, 'DD/MM/YYYY')>=01/01/2022 AND Date(Datefield, 'DD/MM/YYYY')<='31/08/2024';
Select
*
From table;
I want to make this previous month end date condition dynamic for next months, for e.x on October 1 it will from 01/01/2022 and take end date of September as '30/09/2024'
Original date field has values like 2024-03-23
Happy Qliking
Hi, you can try play around with today(), yearstart(), monthend(), monhstart(), addmonths() function and combine them for needed result. In your case it could be:
where year(Datefield)>=year(today())-2 and Datefield<monthstart(today())
More examples on Qlik.help: https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...
Hi thanks for quick response, but it's not working.
Any expression to automate this?
Where (Date(Datefield, 'DD/MM/YYYY')>='01/01/2022'
AND
Date(Datefield, 'DD/MM/YYYY')<='31/08/2024');
My suggested expression (or logic) for your 'automation' already, but maybe due different date format or other things this not work for you. Easiest to check is to load data without where condition and in report level in simple table chart write right sides of conditions, to see does they provide value on your case. Then make comparison with your actual datefield added, to check does comparison work. If yes, put that to your where clause.
A bit adjusted expression could be
WHERE year(Date(Datefield, 'DD/MM/YYYY'))>=year(today())-2
AND
Date(Datefield)<date(floor(monthstart(today())))