Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
jblomqvist
Specialist
Specialist

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

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?

4 Replies
swuehl
MVP
MVP

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

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

Adapt accordingly if you mean May instead of June

Anonymous
Not applicable

try this in script

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

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable

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