Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jocampbell
New Contributor II

How can I check whether a month/day falls within a date range?

Hello all,

I have some data that looks like this:

 

StudentIDDOBStartDateEndDateBirthday?
3142108/11/196022/10/201728/10/2017
4628716/08/200222/10/201728/10/2017
5177128/09/200022/10/201728/10/2017
5177603/11/200422/10/201704/11/2017Yes
5185804/01/196111/10/201718/10/2017

I'm trying to determine whether a student will have a birthday during their course so I need to check whether the month/day of the DOB will fall between the StartDate and EndDate (inclusive). What's the best way of doing this?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: How can I check whether a month/day falls within a date range?

Are you sure this returns the correct result even when the course crosses year change?

I would like to suggest

If( Age(StartDate-1,DOB)<> Age(EndDate,DOB),'Yes','No') as Birthday?

SET DATEFORMAT = 'DD/MM/YYYY';

LOAD *,

If(DayNumberOfYear(DOB) >= DayNumberOfYear(StartDate) and DayNumberOfYear(DOB) <= DayNumberOfYear(EndDate),'Yes','No') as B1?,

If(SetDateYear(DOB, Year(Today())) >= SetDateYear(StartDate, Year(Today())) and SetDateYear(DOB, Year(Today())) <= SetDateYear(EndDate, Year(Today())), 'Yes') as Birthday?,

If( Age(StartDate-1,DOB)<> Age(EndDate,DOB),'Yes','No') as B2?

;

LOAD * INLINE [

    StudentID, DOB, StartDate, EndDate

    31421, 08/11/1960, 22/10/2017, 28/10/2017

    46287, 16/08/2002, 22/10/2017, 28/10/2017

    51771, 28/09/2000, 22/10/2017, 28/10/2017

    51776, 03/11/2004, 22/10/2017, 04/11/2017

    51858, 04/01/1961, 11/10/2017, 18/10/2017

    52222, 01/01/1970, 31/12/2017, 02/01/2018

    52223, 31/12/1970, 31/12/2017, 02/01/2018

    52224, 02/01/1970, 31/12/2017, 02/01/2018   

    52225, 30/12/1970, 31/12/2017, 02/01/2018

    52226, 03/01/1970, 31/12/2017, 02/01/2018   

];

5 Replies

Re: How can I check whether a month/day falls within a date range?

May be like this

LOAD *,

If(SetDateYear(DOB, Year(Today())) >= SetDateYear(StartDate, Year(Today())) and SetDateYear(DOB, Year(Today())) <= SetDateYear(EndDate, Year(Today())), 'Yes') as Birthday?;

LOAD * INLINE [

    StudentID, DOB, StartDate, EndDate

    31421, 08/11/1960, 22/10/2017, 28/10/2017

    46287, 16/08/2002, 22/10/2017, 28/10/2017

    51771, 28/09/2000, 22/10/2017, 28/10/2017

    51776, 03/11/2004, 22/10/2017, 04/11/2017

    51858, 04/01/1961, 11/10/2017, 18/10/2017

];

antoniotiman
Honored Contributor III

Re: How can I check whether a month/day falls within a date range?

If(DayNumberOfYear(DOB) >= DayNumberOfYear(StartDate) and DayNumberOfYear(DOB) <= DayNumberOfYear(EndDate),'Yes','No')

jocampbell
New Contributor II

Re: How can I check whether a month/day falls within a date range?

Brilliant - thank you both!

MVP
MVP

Re: How can I check whether a month/day falls within a date range?

Are you sure this returns the correct result even when the course crosses year change?

I would like to suggest

If( Age(StartDate-1,DOB)<> Age(EndDate,DOB),'Yes','No') as Birthday?

SET DATEFORMAT = 'DD/MM/YYYY';

LOAD *,

If(DayNumberOfYear(DOB) >= DayNumberOfYear(StartDate) and DayNumberOfYear(DOB) <= DayNumberOfYear(EndDate),'Yes','No') as B1?,

If(SetDateYear(DOB, Year(Today())) >= SetDateYear(StartDate, Year(Today())) and SetDateYear(DOB, Year(Today())) <= SetDateYear(EndDate, Year(Today())), 'Yes') as Birthday?,

If( Age(StartDate-1,DOB)<> Age(EndDate,DOB),'Yes','No') as B2?

;

LOAD * INLINE [

    StudentID, DOB, StartDate, EndDate

    31421, 08/11/1960, 22/10/2017, 28/10/2017

    46287, 16/08/2002, 22/10/2017, 28/10/2017

    51771, 28/09/2000, 22/10/2017, 28/10/2017

    51776, 03/11/2004, 22/10/2017, 04/11/2017

    51858, 04/01/1961, 11/10/2017, 18/10/2017

    52222, 01/01/1970, 31/12/2017, 02/01/2018

    52223, 31/12/1970, 31/12/2017, 02/01/2018

    52224, 02/01/1970, 31/12/2017, 02/01/2018   

    52225, 30/12/1970, 31/12/2017, 02/01/2018

    52226, 03/01/1970, 31/12/2017, 02/01/2018   

];

jocampbell
New Contributor II

Re: How can I check whether a month/day falls within a date range?

This is even better! Thank you :-)

Community Browser