topic Re: Check if a date is within a certain range in QlikView

Check if a date is within a certain range

Tue, 27 May 2014 21:45:42 GMT

Hi everyone!

Can you please show me what I can do to validate if a certain date (e.g. my_date) is within a date range?

I saw the function InYearToDate(). However this only checks if the my_date is part of the year containing the basedate.

My problem is what if the date range that I want to use spans 2 different years?

A real life example will be a Fiscal Calendar.

For instance, from May 2013 to April 2014 is my Fiscal Calendar. Now, I need to check if my_date falls within this Fiscal Calendar.

Thanks for all your help!

Re: Check if a date is within a certain range

swuehl — Tue, 27 May 2014 21:55:55 GMT

Maybe like

if(my_date >= makedate(2013,5,1) and my_date <= makedate(2014,4,30), 'Yes','No')

for example used in a load script or expression in a chart with dimension my_date.

Re: Check if a date is within a certain range

Peter_Cammaert — Wed, 28 May 2014 00:38:46 GMT

Or, as a variation, create two calendar objects and attach them to two variables, for instance vStartPeriod and vEndPeriod.

Stefan's expression then becomes:

IF ((my_date >= '$(vStartPeriod)') and (my_date <= '$(vEndPeriod)'), 'YES', 'NO')

And if you want the period to be always exactly 12 months long, start from the first variable (drop the second variable and calendar object) by doing this:

IF ((my_date >= '$(vStartPeriod)') and (my_date < AddMonths('$(vStartPeriod)', 12)) 'YES', 'NO')

and so on...