How can I do this type of date range filter in the script with the current/today's date?

[jblomqvist]

Hi all,

In my script I would like to do the following:

For example, today is 17th May 2016. In the where clause I would like the script to automatically calculate the following:

Start date to be 24 months back from today's date to the start of the month E.g. 01/06/2014

And End Date should be 1 year forwards to the end of the month of the current date. E.g. 31/06/2017.

How can I create these two variables in the script using the current date please?

[swuehl]

Let vStart =Date( Monthstart(Today(),-23));

Let vEnd =Date( MonthEnd(Today(),13));

Adapt accordingly if you mean May instead of June

[Anonymous]

try this in script

start date: monthstart(addmonths(today(),-23)) as Startdate

end date: monthend(addmonths(today(),13) as Enddate

[Oleg_Troyansky]

Hi John,

All of the answers to these questions can be found in a single Help article - "Date and Time Functions".

Something along these lines:

Date >= MonthStart(AddMonths(Today(), -24))

and

Date <= MonthEnd(AddMonth(Today(),12))

For a SQL SELECT statement, assign the same calculations to variable and use the variables within $-sign expansions.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Austin, TX and Johannesburg, South Africa!

[Anonymous]

Hi John,

Try this :

LET vStartDate = MonthStart(AddMonths(Today(),-23))   (makes 01/06/2014 the start date)

LET vEndDate = MonthEnd(AddMonths(Today(),+13))  (makes 30/06/2017 the end date)

Regards,

Filip Duchateau