QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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:

 StudentID DOB StartDate EndDate Birthday? 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 Yes 51858 04/01/1961 11/10/2017 18/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

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

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?

;

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
MVP

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

May be like this

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

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

];

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

New Contributor II

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

Brilliant - thank you both!

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

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?

;

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

];

New Contributor II

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

This is even better! Thank you :-)