QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
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,

Tags (2)
1 Solution

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

12 Replies
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.

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/2012 9/2/2013 1 Years, 3 Months, 19 Days

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

Regards,

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

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

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