topic Re: Calculate Age in Qlikview in QlikView

Calculate Age in Qlikview

Mon, 02 Sep 2013 05:40:32 GMT

Hi,

I'm new to qlikview I want to calculate age or difference between two date like in years,Months and in days

i get 1 solution on qlikcommunity but it didn't work as i want, it shows wrong age as

difference between '30-07-2013' and '30-08-2013' is only 1 month but solution gives me '0 Year 12 Months 29 Days'

which is wrong.

Please find attachments.

Regards,

Re: Calculate Age in Qlikview

Anonymous — Mon, 02 Sep 2013 05:52:58 GMT

Hi,

Have you tried the interval function. it could help you in this scenario..

Interval(DT2-DT1,'dd') as DaysinDiff

try this way..

I hope so it would give you an idea.

Re: Calculate Age in Qlikview

Mon, 02 Sep 2013 06:02:20 GMT

Thank you Hassam for your quick response but i don't think so it would be helpful for me due to leap year.

If any period have leap year it fails and shows wrong no of days.

Re: Calculate Age in Qlikview

christophebrault — Mon, 02 Sep 2013 08:03:30 GMT

Hi,

Can you try this :

num(age(DT2,DT1),'0 years ')&

num(if(num(Month(DT1))<=num(Month(DT2)),

12-((num(Month(DT1))-num(Month(DT2)))+12),

if(year(DT1)<year(DT2),(num(Month(DT2))+12)-(num(Month(DT1))), (num(Month(DT1))-num(Month(DT2))))

),'0 months ')&

num(if(day(DT1)>day(DT2),

(Day(MonthEnd(DT1))-day(DT1))+day(DT2),day(DT2)-day(DT1))

,'0 days')

I get the same result with your field age

Re: Calculate Age in Qlikview

Mon, 02 Sep 2013 08:20:41 GMT

Hi Christophe,

I really sorry to say that your given solution is not working correctly.

after adding below line to data in excel for just checking the logic

5/14/2012

9/2/2013

1 Years, 3 Months, 19 Days

correction show age "1 Years, 4 Months, 19 Days" which is wrong.

Regards,

Re: Calculate Age in Qlikview

christophebrault — Mon, 02 Sep 2013 08:49:33 GMT

Can you try this ?

num(age(DT2,DT1),'0 years ')&

num(if(num(Month(DT1))<=num(Month(DT2)),

12-((num(Month(DT1))-num(Month(DT2)))+if(day(DT1)<=day(DT2),12,13)),

if(year(DT1)<year(DT2),(num(Month(DT2))+12)-(num(Month(DT1))), (num(Month(DT1))-num(Month(DT2))))

),'0 months ')&

num(if(day(DT1)>day(DT2),

(Day(MonthEnd(DT1))-day(DT1))+day(DT2),day(DT2)-day(DT1))

,'0 days')

Re: Calculate Age in Qlikview

Mon, 02 Sep 2013 09:32:36 GMT

Christophe,

Logic is still not working properly, after adding another line to the data,

Sorry for that actually I'm new on qlikview and stuck on that point.

Find attachment for details.

Regards

Re: Calculate Age in Qlikview

swuehl — Mon, 02 Sep 2013 09:42:14 GMT

Maybe like I suggested here?

Re: Calculate Age in Qlikview

Mon, 02 Sep 2013 10:27:52 GMT

Hi Swuehl,

Your logic is still not working as you can see in the attached file.

*DoB=29/02/2008

Regards,

Re: Re: Calculate Age in Qlikview

swuehl — Mon, 02 Sep 2013 10:53:23 GMT

Right, there is an issue with dates in the same month in the old app.

Here is a modified version which should correct for this (I also removed the additional one day count).

Re: Re: Calculate Age in Qlikview

Nagaraju_KCS — Mon, 02 Sep 2013 12:09:23 GMT

Hi Swuehl,

it's not open here, please give me text..

thanks

Re: Re: Calculate Age in Qlikview

Mon, 02 Sep 2013 12:12:44 GMT

*DT1 is DoB

*DT2 is Second Day (Today)

age(DT2,DT1)&' Years '&

if(day(DT2) >= day(DT1),

mod((Year(DT2)*12+Month(DT2))-(Year(DT1)*12+Month(DT1)),12),

if(mod((Year(DT2)*12+Month(DT2))-(Year(DT1)*12+Month(DT1)),12)=0,11,mod((Year(DT2)*12+Month(DT2))-(Year(DT1)*12+Month(DT1)),12)-1)) &' Months '&

if(day(DT2) >= day(DT1),

day(DT2)-day(DT1),

DT2-addmonths(DT1,12*(age(DT2,DT1))+if(mod((Year(DT2)*12+Month(DT2))-(Year(DT1)*12+Month(DT1)),12)=0,11,mod((Year(DT2)*12+Month(DT2))-(Year(DT1)*12+Month(DT1)),12)-1)))

Re: Calculate Age in Qlikview

fashid — Mon, 15 Dec 2014 07:53:17 GMT

Hi swuehl,

I know this would be difficult but it would be really nice if you could please explain the logic behind your script for calculating age for.

I have failed to understand the actual logic of the individual fields that are calculated using functions (the purpose of creating the fields)

Regards,

Nadeem