0

Age Calculation

Jet Reports Historic Posts

September 18, 2013 14:39

None

Follow

Hi All;

i would like to caclulate the age of employees , how can i do that in jet enterprise based on the date of birth.

Regards;

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts September 29, 2013 08:18

  Problem solved with th folowing way :
  
  1 - add a field in the Employee table called Age ( Age = DATTEDIFF(YAER, BIRTH_DATE, GETDATE() ) ).
  2 - add two standar mesure : sum mesure of age , Employee count.
  3 - Add a calculated mesure Average Age : Sum Mesure/ Employee Count.
  4 - Hide the Sum of Age cuz i dont need it.
  
  Result were perfect for me.
  
  Regards ;

• 0
  
  Lenka Kureckova Eriksen October 30, 2017 08:32

  Hi,

  I have a question - how would you do this, if you do not just want to know "as of today" (getdate), but should be able to chose a date or see development accross time.

  Thank you! 

• 0
  
  JaeW_atOnyx October 30, 2017 12:19

  It's tough b/c In the earlier solution you were building the date into the fact table which of course creates a static number.

   Age = DATTEDIFF(YAER, BIRTH_DATE, GETDATE() ) 

  If you want to have a dynamic date you have to use a variation of .CurrentMember and figure out how to extract the Member Value (instead of Name or Key) in a calculated measure

  https://docs.microsoft.com/en-us/sql/mdx/currentmember-mdx

  SSAS MDM is not super intuitive when it comes to date calculations, so it may be easiest to

  1. have birthYear represented on your Customer table as an integer.
  2. Add BirthYear to the Customer dimension.
  3. Create a Calculated Measure of [Posting Date].[Year].CurrentMember.member_value - [Customer].[Birth Year].CurrentMember.member_value

  For greater precision depending on what month of the year we're looking at, instead of doing calculations based on a calendar year, you may have to consider how Excel or R handle dates (as Integers instead of true DateValue

  • where birthdate is represented as the number of days since some origin date eg. 1/1/1970 
  • consider how R handles it:  https://stats.idre.ucla.edu/r/faq/how-does-r-handle-date-values/
  • In my example 1/1/1970 = 1, if you were born 12/31/1969 you have -1, and if you were born 1/2/1970 you have 2.
  • you can still do the mathematical difference (which will then be in units of days and then you have to modulo divide to show whole years.

   

  Hope that pseudocode gets you going.

  Jae@onyxReporting.com

• 0
  
  Lenka Kureckova Eriksen October 31, 2017 10:03

  Thank you very much Jae!

  I haven't made it work yet, but your answer is definitelly very useful. Had no clue about CurrentMember.

   

  Best regards, 

  Lenka