topic calculate date based on future age in QlikView

calculate date based on future age

rittermd — Wed, 04 Feb 2015 13:16:39 GMT

Here is my challenge.

I have a DOB for a client. I need to calculate the correct date based on their future age. For example person is born on 12/31/1975. I need to know the future date when they turn 12 years and 6 months old.

I tried just adding 150 months to their DOB. That works most of the time. But in this example it doesn't work because it put the date in June that only has 30 days. So my calculation will show June 30 1987. It should actually show a day in July. So just adding months does not take into account the number of days in each month.

What can I do?

Re: calculate date based on future age

Anonymous — Wed, 04 Feb 2015 13:21:20 GMT

Hi,

Maybe add/minus 1 day if number of days in a month is not equal to number of days in a future month.

Regards

Neetha

Re: calculate date based on future age

robert_mika — Wed, 04 Feb 2015 13:27:40 GMT

Did you try

=AddMonths('31-2-1975',150)

Re: calculate date based on future age

hic — Wed, 04 Feb 2015 13:34:40 GMT

Depends on what you mean by 12 years and 6 months.

"A month" could mean the number of days the actual calendar month has, and then you should use AddMonths(). But it could also mean e.g. the average month length: 30,43687 days. Similarly, "a year" could be the number of days the calendar says, or it could be 365,2424. (Adding days is simple. Just use Date + <NumberOfDays>.)

So, just saying 12 years and 6 months is ambiguous.

You need to define what you mean, and then use a combination of adding years using AddYears(), adding months using AddMonths(), and adding days using simple addition.

HIC