Calculating Age

[qv_testing]

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

[qv_testing]

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]

Exactly, that is the problem.

But i have to fix up that.

Any other solution ???

[sunny_talwar · Accepted solution]

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

];