General OBIEE

Get Involved. Join the Conversation.

Topic

    Srinivas Challa
    OTBI: Diff of Two dates to get Age is rounded to next...
    Topic posted December 21, 2018 by Srinivas Challa, tagged Analyses, Core HR, Fusion, OBI Answers, OBIEE Answers, OTBI, Report Output 
    972 Views, 2 Comments
    Title:
    OTBI: Diff of Two dates to get Age is rounded to next highest value
    Summary:
    OTBI: Diff of Two dates to get Age is rounded to next highest value
    Content:

    OTBI: Diff of Two dates to get Age is rounded to next highest value

    Example: If difference of Age is 48.8 Its displayed as 49 instead of 48 even after using FLOOR or TRUNCATE.

    Below is the formula I used still unable to succeed.

    FLOOR(TimeStampDiff(SQL_TSI_YEAR, "Dependent Data"."Dependent Date Of Birth", Current_Date))

    Your help is highly appreciated.

    Comment

     

    • Srinivas Challa

      The above issue is in OTBI Analysis. Please help.

    • Prudence Kruchten

      Srinivas - please try this formula:

      FLOOR((TIMESTAMPDIFF(SQL_TSI_DAY, "Person"."Person Date Of Birth", CURRENT_DATE))/365.25)

      I could not get anything else to work, but this one seems to do it.  It's interesting that Oracle's recommendation is just TIMESTAMPDIFF(SQL_TSI_DAY, "Person"."Person Date Of Birth", CURRENT_DATE) as this will calculate with the rounding, yet I know that when we report birthdays, we report what has passed and thus someone would be 48 not 49 if they are 48.8 years old :)

      I'd be curious if others have formulas that work too?