topic Getting the number of days between to dates in QlikView

Getting the number of days between to dates

Anonymous — Mon, 28 Jul 2014 07:33:06 GMT

Hi.
I am working with showing a list of upcoming birthdays. I have fields with the the date of birth that i can use to do this. I am able to calculate the age using the age() function. I also want to calculate how many days it is until each persons birthday, but I am unable to figure out how to do this.

Re: Getting the number of days between to dates

Mon, 28 Jul 2014 07:38:21 GMT

Hi Dagrun,

You can use Date of Birth(field) - today() to get the number of days until each persons birthday.

Thanks

Sabal

Re: Getting the number of days between to dates

VishalWaghole — Mon, 28 Jul 2014 07:53:45 GMT

Hi Sabal,

Try this,

Date(Today(),'MM-DD-YYYY') - Date(Date_of_Birth,'MM-DD-YYYY') as NoOfDays

Hope this will work for you.

-- Regards,

Vishal Waghole

Re: Getting the number of days between to dates

Anonymous — Mon, 28 Jul 2014 07:54:39 GMT

When I did that I got some strange output.

What I found almost worked was using:
DayNumberOfYear(date(dateOfBirth)) - DayNumberOfYear(date(today))

This does give me the number of days until the birthday in the current year, but if the birthday has already happened this year I want it to calculate the number of days until the birthday in the next year. Other ways I will get strange output for the number of days till the closest birthday in the end of December

Re: Getting the number of days between to dates

Anonymous — Mon, 28 Jul 2014 09:02:34 GMT

Oh and I guess there will also be a problem if the person was born in a leap year, but the current one is not a leap year.

Re: Getting the number of days between to dates

simenkg — Mon, 28 Jul 2014 09:19:57 GMT

if(Date(DateOfBirth)<Today(), num(Addmonths(Date(DateOfBirth),12)) - num(Today()),

num(Date(DateOfBirth)) - num(Today())) as NoOfDays,

Re: Getting the number of days between to dates

Mon, 28 Jul 2014 09:21:01 GMT

Hi Dagrun,

Try this:

=If(MakeDate(Year(Today()),Month(dateOfBirth),Day(dateOfBirth))>=Today(),MakeDate(Year(Today()),Month(dateOfBirth),Day(dateOfBirth))-Today(),MakeDate(Year(addyears(Today(),1)),Month(dateOfBirth),Day(dateOfBirth))-Today())

Hope it works.

If not, just check for the "dateOfBirth" format.

Thanks

Sabal

Re: Getting the number of days between to dates

Mon, 28 Jul 2014 09:43:41 GMT

Hi there.

I would simply do the following:

LET vYear = right(Today(), 7);

TEMP:

LOAD *

left(DOB, 2) & '/' & mid(DOB, 4,2) & '/' & vYear as [DOB Calculation]

FROM xxx.qvd;

DATA:

NOCONCATENATE

LOAD *,

[DOB Calculation] - Today() as [Days to go]

RESIDENT TEMP;

DROP TABLE TEMP;

Regards,
Marius

Re: Getting the number of days between to dates

Anonymous — Mon, 28 Jul 2014 10:17:51 GMT

This worked. thank you