Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
qv_testing
Specialist II
Specialist II

Calculating Age

Hi Community,

I am facing issue in Age Calculation, Days its showing 2,3 days different.

This is my Age calculation like (Year-Month-Days).

load *, Today()-[Date of Joining] as [Age_in_Days];

LOAD * INLINE [

    Employee Code, Name, Date of Joining, Gender

    10002, Raju, 12/1/2008, M

    10006, Sunil, 1/10/2009, M

    10007, Sami, 3/22/2012, F

    10012, Laxmi, 6/28/2013, F

    10013, Swetha, 6/20/2011, F

    10021, Nithin, 7/3/2007, M

    10024, Santosh, 7/30/2007, M

];

PFA,

Thanks in Advance....

1 Solution

Accepted Solutions
sunny_talwar

Try this logic provided by Stefan (Calculate Age in Qlikview‌)

LOAD *,

  If(Day(Today()) >= Day([Date of Joining]), Mod(MonthIDToday-MonthIDDoJ, 12),

  If(Mod(MonthIDToday-MonthIDDoJ,12) = 0, 11, Mod(MonthIDToday-MonthIDDoJ,12)-1)) as AgeMonths,

  If(Day(Today()) >= Day([Date of Joining]), Day(Today()) - Day([Date of Joining]),

  Today() - AddMonths([Date of Joining], 12 * AgeYears + If(Mod(MonthIDToday-MonthIDDoJ,12) = 0, 11, Mod(MonthIDToday-MonthIDDoJ,12)-1))) as AgeDays;

LOAD *,

  Age(Today(), [Date of Joining]) as AgeYears,

  Year([Date of Joining])*12+Month([Date of Joining]) as MonthIDDoJ,

  Year(Today())*12+Month(Today()) as MonthIDToday;

LOAD * INLINE [

    Employee Code, Name, Date of Joining, Gender

    10002, Raju, 12/1/2008, M

    10006, Sunil, 1/10/2009, M

    10007, Sami, 3/22/2012, F

    10012, Laxmi, 6/28/2013, F

    10013, Swetha, 6/20/2011, F

    10021, Nithin, 7/3/2007, M

    10024, Santosh, 7/30/2007, M

];

View solution in original post

4 Replies
qv_testing
Specialist II
Specialist II
Author

Here, one particular case...

Santosh -- Age should be- 9 years 3 days

My calculation showing 9 years 6 days

sunny_talwar

I think you are looking to get an exact difference using approximate number of days in a year and month. For instance some years (such as 2016) has 366 days and similarly not all months are 30 days. I think the difference is due to these approximations you have made.

qv_testing
Specialist II
Specialist II
Author

Exactly, that is the problem.

But i have to fix up that.

Any other solution ???

sunny_talwar

Try this logic provided by Stefan (Calculate Age in Qlikview‌)

LOAD *,

  If(Day(Today()) >= Day([Date of Joining]), Mod(MonthIDToday-MonthIDDoJ, 12),

  If(Mod(MonthIDToday-MonthIDDoJ,12) = 0, 11, Mod(MonthIDToday-MonthIDDoJ,12)-1)) as AgeMonths,

  If(Day(Today()) >= Day([Date of Joining]), Day(Today()) - Day([Date of Joining]),

  Today() - AddMonths([Date of Joining], 12 * AgeYears + If(Mod(MonthIDToday-MonthIDDoJ,12) = 0, 11, Mod(MonthIDToday-MonthIDDoJ,12)-1))) as AgeDays;

LOAD *,

  Age(Today(), [Date of Joining]) as AgeYears,

  Year([Date of Joining])*12+Month([Date of Joining]) as MonthIDDoJ,

  Year(Today())*12+Month(Today()) as MonthIDToday;

LOAD * INLINE [

    Employee Code, Name, Date of Joining, Gender

    10002, Raju, 12/1/2008, M

    10006, Sunil, 1/10/2009, M

    10007, Sami, 3/22/2012, F

    10012, Laxmi, 6/28/2013, F

    10013, Swetha, 6/20/2011, F

    10021, Nithin, 7/3/2007, M

    10024, Santosh, 7/30/2007, M

];