Where condition from date field

[raZor]

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

[justISO]

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

[raZor]

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');

[justISO]

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())))

[raZor · Accepted solution]

I tried MonthEnd(AddMonths(Today(),-1)) for getting 31/08/2024 when we are in Sept.