Age Calculation based on DOB

[ExApplix]

Hi,

I have got DOB (Date of Birth) in the cube as String format 'YYYY-MM-DD' e.g. 1961-12-23

In TM1 Rile, I want to calculate the Age of person based on Today's date.

Can someone please give me the calculation how to get it?

Thanks in advance

[David Usherwood]

Looking through the date and time rules functions in the Reference Guide, I see
DAYNO

This is a TM1® rules function, valid in both TM1 rules and TurboIntegrator processes.

DAYNO returns the serial date number corresponding to a given date string.

and

NOW

This is a TM1® rules function, valid in both TM1 rules and TurboIntegrator processes.

NOW returns the current date/time value in serial number format.

The 'Serial Number Format' is the number of days since (from memory) 1/1/60.
Seems to cover what you need.

[lotsaram]

Reading between the lines, a liberal interpretation of what David has said is that you have been around TM1 long enough that given the tools at your disposal (a quick glance through the date and time rules functions section of the reference guide in this case) you should be able to go and figure this out for yourself without requesting help and wasting people's time.

I'm inclined to be more direct: given the length of time you have been posting on this board and the length of time you have been exposed to TM1 I would have thought you could figure this out for yourself.

I hope you don't find that too harsh. To take the sting out here's a simple example of how you might do the calculation to return a decimal year.

Code: Select all

['Age in Years'] = N: 
( NOW() - DAYNO(DB('EmployeeData', !Employee, 'DOB')) ) / 365;

I hope you agree that was not too difficult.
(My apologies to you David if I was putting any words in your mouth that weren't actually stuck in your throat!)

[ExApplix]

Ya I tried to convert the Date using DAYNO and subtract the serial number of the Today() but the answer which I get does not seem to be right.

Can someone please give me syntax here?

Thanks

[lotsaram]

Ya I tried to convert the Date using DAYNO and subtract the serial number of the Today() but the answer which I get does not seem to be right.

Can someone please give me syntax here?

Thanks

I have given you the correct syntax for the problem which you have described. TODAY() does not return a serial date, it returns a formatted date string, hence use of the NOW() function. If you need to calculate a date based not on the current time but on a date calculated by a a dimension element, intersection, or a string value then you would need to reuse DAYNO. If you need further assistance I suggest you re-read the posts above.

[ExApplix]

I'm inclined to be more direct: given the length of time you have been posting on this board and the length of time you have been exposed to TM1 I would have thought you could figure this out for yourself.

Lotsaram, Thanks for the reply but I just want to say one thing that sometimes we get stuck in really simple things or it could be the first time we are dealing with something. Also, we all come here on this forum to help each other. So there is no point for getting rude or harsh to anyone. So please dont get anything from this forum on your nerves.

Getting back to the actual question:
I came to knew that TM1 dates cant handle dates prior t0 1960, so if the DOB is prior to 1960 then how can we handle this? Is there a special function to do this?

[lotsaram]

Getting back to the actual question:
I came to knew that TM1 dates cant handle dates prior t0 1960, so if the DOB is prior to 1960 then how can we handle this? Is there a special function to do this?

I see. Then DAYNO won't work for you. Date indexing starts in TM1 from 01 Jan 1960. Where the date indexing starts from is a completely arbitrary decision, it has to start from somewhere but why 01.01.1960 who knows? Alan Kirk has posted about this at great length so I won't go into it further.

The simplest way to tackle this problem is to subtract the time serial numbers of two dates and divide by 365 to convert days into years. However if you have a date prior to the commencement of the date index then this won't work and you will need to resort to a moderately more complex method involving sub-stringing your formatted dates and converting the years into integers and the months to fractions to add to the years then do the subtraction. The length that you need to go to depends on the accuracy that you require, ... you could also convert the days to a month fraction and if you wanted to go the whole way and be absolutely accurate then you would need lookup cubes to grab the correct number of days depending on the month etc.

But here's a simplified example:

Code: Select all

['Age in Years'] = N:
( TIMVL(NOW(), 'Y') + ( TIMVL(NOW(), 'M') / 12 ) )
 - 
(
  NUMBR(SUBST(DB('EmployeeData', !Employee, 'DOB'), 1, 4))
  +
  ( NUMBR(SUBST(DB('EmployeeData', !Employee, 'DOB'), 6, 2)) / 12 ) 
);

Had you explained your problem in more detail and the lengths you had already taken to solve it and given an example of your code (read the request for assistance guidelines) then my original response would have been different. A number of people including myself are happy to assist others but I think I speak for all when I say that I am much happier to offer assistance when there is a demonstrated effort on behalf of the person asking for help to have actually done some thinking about the problem and taken some steps themselves to solve it rather than reaching out for help immediately as a first resort. Free-loading lessens the willingness of others to offer assistance. Especially for people who have been around for a while and are asking for assistance I think it is imperative to show that some sort of effort has been made. It is also nice to once in a while contribute back to the community by answering questions as well.

[tomok]

A number of people including myself are happy to assist others but I think I speak for all when I say that I am much happier to offer assistance when there is a demonstrated effort on behalf of the person asking for help to have actually done some thinking about the problem and taken some steps themselves to solve it rather than reaching out for help immediately as a first resort. Free-loading lessens the willingness of others to offer assistance. Especially for people who have been around for a while and are asking for assistance I think it is imperative to show that some sort of effort has been made. It is also nice to once in a while contribute back to the community by answering questions as well.

Preach on, brother!

[MarenC]

Hi,

maybe its me but I just couldn't get DayNO or TimVl to work, too many out by 1.

So I did the following (my date is in dd/mm/yyyy format):

Code: Select all

['EmpAge']=S:		
	If(		
		Month(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) = Month(Today(1)) & 	
		Day(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) > Day(Today(1))	
		,	
			Str( NUMBR(Subst(Today(1),1,4)) -1 - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0),
	If (		
		Month(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) > Month(Today(1)),	
			Str( NUMBR(Subst(Today(1),1,4)) -1 - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0),
			Str( NUMBR(Subst(Today(1),1,4)) - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0)
	));

I posted this because I don't know why Dayno or Timvl were suggested and wondered if I was missing something!

I was also confused as to why I needed to convert this numeric attribute to string but I understand all attributes are actually string, so any rules in the attributes cube must be S?

Maren

[tomok]

Hi,

maybe its me but I just couldn't get DayNO or TimVl to work, too many out by 1.

So I did the following (my date is in dd/mm/yyyy format):

Code: Select all

['EmpAge']=S:		
	If(		
		Month(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) = Month(Today(1)) & 	
		Day(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) > Day(Today(1))	
		,	
			Str( NUMBR(Subst(Today(1),1,4)) -1 - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0),
	If (		
		Month(	
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
			Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2)
		) > Month(Today(1)),	
			Str( NUMBR(Subst(Today(1),1,4)) -1 - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0),
			Str( NUMBR(Subst(Today(1),1,4)) - NUMBR(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4)) ,3,0)
	));

I posted this because I don't know why Dayno or Timvl were suggested and wondered if I was missing something!

I was also confused as to why I needed to convert this numeric attribute to string but I understand all attributes are actually string, so any rules in the attributes cube must be S?

Maren

A ridiculuous amount of work when all you have to do is store Age as a numeric serial number. You can display a serial number in "dd/mm/yy", or "mm/dd/yyyy", or whatever, format simply by applying the proper date format to the attribute cube.

[MarenC]

Hi,

Thanks for taking the time to respond tomok.

I did ask if I had missed something! Which I still must be because I can't work out how your method gets me someones age, I can only apologise for that!

So are you saying to store their Date of Birth as a serial date in the attribute cube and format this element (Date of Birth) as required.

But how does that get me any nearer to then saying if this employee is 30, 29 or 52?

Maren

[tomok]

Hi,

Thanks for taking the time to respond tomok.

I did ask if I had missed something! Which I still must be because I can't work out how your method gets me someones age, I can only apologise for that!

So are you saying to store their Date of Birth as a serial date in the attribute cube and format this element (Date of Birth) as required.

But how does that get me any nearer to then saying if this employee is 30, 29 or 52?

Maren

So, you know the serial number of today's date, the serial number of their birthday, and the fact there are 365 days in a year. Do I actually need to do the math for you?

[Wim Gielis]

So, you know the serial number of today's date, the serial number of their birthday, and the fact there are 365 days in a year. Do I actually need to do the math for you?

I would first of all divide by 365.25 instead of 365. Where I live, we have the concept of leap years.
After that, apply some rounding too. The answer will never be fully accurate in all cases, though.

[tomok]

So, you know the serial number of today's date, the serial number of their birthday, and the fact there are 365 days in a year. Do I actually need to do the math for you?

I would first of all divide by 365.25 instead of 365. Where I live, we have the concept of leap years.
After that, apply some rounding too. The answer will never be fully accurate in all cases, though.

Yes, of course but I was going on the assumption that the OP wanted age in years, not fractional years. Using 365 would be accurate except for 1 day every four years.

[Wim Gielis]

Yes, of course but I was going on the assumption that the OP wanted age in years, not fractional years. Using 365 would be accurate except for 1 day every four years.

When doing a DOB calculation, why would one ever want to use fractional years ?

Put differently, I was born January 19,1981. So I turned 39 on January 10, 2020, or on January 19, 2020 ?
Who would like to have such an inaccurate answer ?

[Alan Kirk]

Yes, of course but I was going on the assumption that the OP wanted age in years, not fractional years. Using 365 would be accurate except for 1 day every four years.

When doing a DOB calculation, why would one ever want to use fractional years ?

Put differently, I was born January 19,1981. So I turned 39 on January 10, 2020, or on January 19, 2020 ?

I'd be quiet about that if I were you; you could be skewering your chances to get in as FC Lucyvo's next striker. There may still be hope for you as a no nonsense centre back, though. The oldest, er, I mean, most mature Italian player I know of (in Serie B if I recall correctly) was a 45 year old GK who the rest of the team called "Nonno" (grandpa). Yes, 45. That's depressing.

Who would like to have such an inaccurate answer ?

Agreed. It can also have other effects as well though admittedly they're edge cases. For example some government benefits, tax concessions, etc kick in at an exact number of years, which can have an impact on calculations within a payroll budgeting model. To give a random example in Australia if you are over... 50, I think it is? (I don't have the model up at the moment) you need to get an extra week's pay if you are made redundant. In cases like that calculating age is such a pain in the backside because of leap years (and the omission of leap years in some centuries) that I don't even bother doing it. I'd rather take the day, month and year components from the birth date, whack {50} (or whatever; because the law can change I have this sort of thing as a value in a system cube) onto the year, then just work out whether the resulting serial date is < the current one. This calculation is kept in a cube which contains employee master data, just as a 1 if the rule applies, 0 if not.

Realistically or at least theoretically, in Australia at least, that kind of calculation should be the ONLY reason for working out an age. Under the anti-discrimination law asking a person's age in an interview is off limits, and discriminating against someone on the basis of age is also off limits... up to a point. To use the example above you would be hard pressed to say that a person's age is immaterial to the coach / manager of a calcio team. "You're 49, and you want to be my new box to box midfielder? Yyyyeah, I gotta tell you, nonno, I foresee some complications here." However that aside, in a normal industrial or commercial firm the question would need to be asked about why a manager would need to know the ages of his employees if not for the purpose of culling. Which is, of course, illegal. Here, anyway. In theory, anyway.

Of course anyone is free to ask me my age. If I'm in a good mood I'll say "over 21". If not, it's more likely to be "**** off and mind your own gorram business, and yes, Ferrari SHOULD have picked me over Sainz".

Interestingly Excel has the same basic problem. I don't know which genius at Microsoft wrote this document, but:

Excel can help you calculate the age of a person in different ways. The table below shows common methods to do so, using the Date and time functions.
=(YEAR(NOW())-YEAR(A2))
The result is the age of person—the difference between today and the birthdate in A2.

In the immortal words of my poker buddy Brock Samson, "ARE YOU FREAKIN' KIDDING ME??" For someone whose birthdate is 31 December, that will give the wrong answer for 364 out of 365 days most years, and 365 out of 366 the other ones. If the person has yet to have their birthday in the current year, it will again be wrong. The solution in Excel is of course the DateDif function, which you WON'T find in the formula wizard because according to Microsoft it's there for Lotus compatibility only, and may return the wrong value in some cases (though not, as far as I'm aware, for age calculations). So rather than, oh, I dunno, FIXING the function so that it DOESN'T ever return an error, or alternatively creating an explicit Age() function, MS finds it easier to just pretend it isn't there.

Though that does raise the question of whether an Age() TI/Rules function might be a useful target for an RFE in TM1. I'm not sure that I have enough skin in that game to bother with it, but others may.

[MarenC]

Hi,

The need for this is to drive payroll calculations/forecasts. So while it doesn't have to be 100% accurate I would rather not have the conversation
with users about why it isn't!

Because the DOB was already being held as a text attribute in dd/mm/yyyy format I wanted to calculate the age based on this existing data.

I did try to simplfy the formula as follows:

Code: Select all

['EmpAge']=S:
	Str(Int((Now() - 
	Dayno(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
		Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
		Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2))) \ 365.25),3,0);

However as suggested above anyone born before 1960 and it returns zero.

I am left with my original formula which takes each component of the Age, Year, Month and Day. It may be long winded
but it seems to be calculating correctly.

Now for a forecast I need to know what someone's age will be by year/month into the future so I think I will ultimately hold this in a cube
but was just wanting to nail down the principle before getting my hands too dirty.

I appreciate everyone taking time to help me out.

Maren

[Alan Kirk]

Hi,

The need for this is to drive payroll calculations/forecasts. So while it doesn't have to be 100% accurate I would rather not have the conversation
with users about why it isn't!

Because the DOB was already being held as a text attribute in dd/mm/yyyy format I wanted to calculate the age based on this existing data.

I did try to simplfy the formula as follows:

Code: Select all

['EmpAge']=S:
	Str(Int((Now() - 
	Dayno(Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),7,4) | '-' |
		Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),4,2) | '-' |
		Subst(DB('}ElementAttributes_Employee',!Employee,'Date of Birth'),1,2))) \ 365.25),3,0);

However as suggested above anyone born before 1960 and it returns zero.

You may want to consider switching on the UseExcelSerialDate=T parameter in your config file. That will use Excel's 1900 starting point, and I'm assuming that none of your employees are 120 years old.

(Why wasn't this raised above? Because the earlier posts in this thread are from 2011, 9 years ago. It didn't exist then.)

Enables the use of Microsoft Excel serial dates instead of TM1® serial dates.

Parameter type: optional, static

When UseExcelSerialDate is enabled, TM1 rule functions and TurboIntegrator functions use Jan 1, 1900 as a base date for serial dates instead of Jan 1, 1960.

In the past, TM1 rule functions used serial dates that represent the number of days elapsed since Jan 1, 1960. This conflicts with Microsoft Excel serial dates, which represent the number of days elapsed since Jan 1, 1900. The number formatting features in TM1 expect cube data to use Microsoft Excel serial dates rather than TM1 serial dates.

To avoid the need to convert dates, enable UseExcelSerialDate to have rule functions use Microsoft Excel dates rather than legacy TM1 dates.

Be thee warned!

You will need to test the living hell out of this change in your dev server to make sure that this doesn't break any existing code or rules before you make the change on your prod server.

[gtonkin]

I do something different. I basically treat months and days as decimals on the year and then do some subtraction and rounding to get the age.

Code: Select all

['Age']=N:
INT(
(NUMBR(SUBST(TODAY(1),1,4))+
NUMBR(SUBST(TODAY(1),6,2))\12+
NUMBR(SUBST(TODAY(1),9,2))\365.25)
-
(NUMBR(SUBST(DB('Employee',!Employees,'DOB'),1,4))+
NUMBR(SUBST(DB('Employee',!Employees,'DOB'),6,2))\12+
NUMBR(SUBST(DB('Employee',!Employees,'DOB'),9,2))\365.25)
);

When you test the above with someone who was born on 1920-06-11, you get 99 years old and when you set the DOB to 1920-06-10, you get 100 as expected. Seems to work consistently, tested as far back as 0020-06-11 to get 1999 and 0020-06-10 to get 2000. YMMV

p.s as the months and days are reduced to fractions, the 29th of February is taken care of and people age very close to the 1st of March