Calculate Age

mrmmickle1

Hello,

I'm trying to replicate a VB Function for age.  I can't seem to get the syntax right... can someone please assist me?

WHAT I HAVE (INVALID SYNTAX):

CASE WHEN IFNULL("Employee Personal Attributes"."Employee Birth Date") THEN 0 WHEN (MONTH("Employee Personal Attributes"."Employee Birth Date")) < CURRENT_DATE) Or MONTH(CURRENT_DATE) AND DAY("Employee Personal Attributes"."Employee Birth Date") <= DAY(CURRENT_DATE) THEN TIMESTAMPDIFF(SQL_TSI_YEAR, "Employee Personal Attributes"."Employee Birth Date", CURRENT_DATE) ELSE TIMESTAMPDIFF(SQL_TSI_YEAR, "Employee Personal Attributes"."Employee Birth Date", CURRENT_DATE)-1 END

VB FUNCTION:

Public Function AgeInYears(ByVal pvarBirthDate As Variant, ByVal pvarCompareDate As Variant) As Long

' Purpose:  Calc age in years as of the compare date.

' Accepts:  pvarBirthDate date

' pvarCompareDate    date

' Returns:  number

' Returns 0 if any needed fields blank.

On Error GoTo Err_AgeInYears

   

' Exit if some fields null.

If IsNull(pvarBirthDate) Or IsNull(pvarCompareDate) Then

  AgeInYears = 0

  Exit Function

End If

   

If (Month(pvarBirthDate) < Month(pvarCompareDate)) Or (Month(pvarBirthDate) = Month(pvarCompareDate) And Day(pvarBirthDate) <= Day(pvarCompareDate)) Then

  AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate)

Else

  AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate) - 1

End If

   

Exit Function

Err_AgeInYears:

MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) & Err.Description & ".", vbExclamation

Exit Function

End Function

Syedsalmancs110

Try below formula:

CASE WHEN "Employee Personal Attributes"."Employee Birth Date" IS NULL THEN 0 ELSE  CASE WHEN  (MONTH("Employee Personal Attributes"."Employee Birth Date") <= MONTH(CURRENT_DATE) AND DAY("Employee Personal Attributes"."Employee Birth Date") <= DAY(CURRENT_DATE)) THEN  TIMESTAMPDIFF(SQL_TSI_YEAR,"Employee Personal Attributes"."Employee Birth Date",CURRENT_DATE)  ELSE  TIMESTAMPDIFF(SQL_TSI_YEAR,"Employee Personal Attributes"."Employee Birth Date",CURRENT_DATE)-1  END END

I tried it on SampleApp for Customer Age Calculation and came out correct:

Pedro F

You had different issues in your formula such as comparing different things. For example, MONTH("Employee Personal Attributes"."Employee Birth Date")) < CURRENT_DATE. You're comparing a Month (number) to a date so it's obvious the formula won't work.

A solution was already provided to you but I have a different question. Why do this in OBIEE and not make this calculation in your ETL process? It seems you have an employee dimension so this would be just another attribute that should just require a minor change instead of forcing OBIEE to send queries to the server to do these calculations for each analysis you build with it.

Thomas Dodds

+1 

"A solution was already provided to you but I have a different question. Why do this in OBIEE and not make this calculation in your ETL process? It seems you have an employee dimension so this would be just another attribute that should just require a minor change instead of forcing OBIEE to send queries to the server to do these calculations for each analysis you build with it."

^ recalc'd with EVERY ROW processed in every report run.

mrmmickle1

@Thomas Dodds

We have Employee Age available in a different Subject Matter Area (EOM Frozen data).  Which is not the one I need to use.  Would like to avoid doing subsequent calculations....

mrmmickle1

@Syed Hamd Salman,

Thank you for the Case Statement this works perfectly.  I appreciate the assistance.  I can see how to nest these Case Statments now and use  'IS NULL' rather than IFNULL() (I also neglected to input the second argument for this haha)  A lot of room for improvement.

@ Pedro F,

You're right.  Definitely missed the Month() function on that portion of the Case Statement.  Good Point.

Thank you all for the responses and for helping point out some of the issues with my original logic!!!

Pedro F

Sorry to insist on this but there's still something I don't follow completely. You say you have Employee Age on another Subject Area. Where does that Employee Age comes from? Because Employee Age should be an attribute in your Employee Dimension and therefore you should be able to use it in any Subject Area you create without any extra work.

mrmmickle1

I can tell you that IT controls our ETL and I am in an Analytics Function.  When we want changes we go to IT and then they draft up requirements etc.....  I'm guessing the idea was it would be easier to base age on a static date.  In the Subject Area I need to use now it's common for criteria to contain different time period data, which could possibly complicate calculations based on different joins.  In addition my company views Age / Employee Birth Date as a very private field (For example HR is the only function with access to this information...)  When the requirements for the Age field were presented maybe the IT department said we can do Age in X Subject Matter now and plan for the Age field in X Subject Area later due to additional complexity.  I was not part of the conversation and can only work with the information available to me.

I'm also not a subject matter expert and OBI and don't plan to be, I will happily give IT that responsibility!

Christian Berg-0racle

mrmmickle1 wrote:I can tell you that IT controls our ETL and I am in an Analytics Function. When we want changes we go to IT and then they draft up requirements etc.....

Once upon a time there were the 80s. Silo'ed, waterfall-driven organizations were common-place. Nobody lived happily ever after.

Thomas Dodds

"I can tell you that IT controls our ETL and I am in an Analytics Function."

You control the WHAT of ETL ... they only control the HOW.  You are here because WHAT isn't getting it done!

"I was not part of the conversation and can only work with the information available to me." 

Foisted requirements ... fails every time.  Sad you have been foisted upon.

"I'm also not a subject matter expert and OBI and don't plan to be, I will happily give IT that responsibility!"

Don't perpetuate the cycle ... you are the subject matter expert - you are the one USING INFORMATION. 

Use of information DRIVES DESIGN