Calculate Age in Qlikview

[#1]

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,

[Anonymous]

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.

[#3]

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.

[christophebrault]

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

[#5]

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,

[christophebrault]

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

[#7]

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

[swuehl]

Maybe like I suggested here?

[#9]

Hi Swuehl,

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

*DoB=29/02/2008

Regards,

[swuehl · Accepted solution]

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