Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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.

Please find attachments.

Regards,

1 Solution

Accepted Solutions
MVP
MVP

Re: Re: Calculate Age in Qlikview

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

View solution in original post

12 Replies
hassam_ahmed
New Contributor III

Re: Calculate Age in Qlikview

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

Re: Calculate Age in Qlikview

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
Valued Contributor

Re: Calculate Age in Qlikview

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

Not applicable

Re: Calculate Age in Qlikview

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/20129/2/20131 Years, 3 Months, 19 Days

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

Regards,

christophebrault
Valued Contributor

Re: Calculate Age in Qlikview

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

Not applicable

Re: Calculate Age in Qlikview

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
MVP

Re: Calculate Age in Qlikview

Maybe like I suggested here?

Not applicable

Re: Calculate Age in Qlikview

Hi Swuehl,

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

*DoB=29/02/2008

Regards,

MVP
MVP

Re: Re: Calculate Age in Qlikview

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

View solution in original post