Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Calculate Age in Qlikview

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.

Regards,

1 Solution

Accepted Solutions
MVP

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

12 Replies
Anonymous
Not applicable
Author

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.

Not applicable
Author

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.

Specialist

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

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Not applicable
Author

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,

Specialist

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

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Not applicable
Author

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

MVP

Maybe like I suggested here?

Not applicable
Author

Hi Swuehl,

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

*DoB=29/02/2008

Regards,

MVP

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

Community Browser