Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to create a measure in OBIEE

Received Response
52
Views
3
Comments
Randy Walker
Randy Walker Rank 1 - Community Starter

Hi,

Currently in OBIEE, there are some Req Aging and Submission Aging measures.

e.g. Average Time from 1st Sourcing to 1st Candidate at Review.

I would like to create one that is instead from 1st Sourcing to Date Created.

Average Time from Date Created to 1st Candidate at Review

Average Time from Date Created to 1st Candidate at Interview

Average Time from Date Created to 1st Candidate at Offer

If anyone can help in how should I go about writing the formula for this measure, that will be much appreciated.


Thank you,

Randy Walker

Tagged:

Answers

  • Hello Randy,

    The function you're looking for is TIMESTAMPDIFF. Its result is a numerical one which you can aggregate as you need. Also you can influence its time horizon according to your needs (days, months, years,..).

    Syntax:

    TIMESTAMPDIFF(interval, timestamp1, timestamp2) 

    Intervals:

    • SQL_TSI_SECOND
    • SQL_TSI_MINUTE
    • SQL_TSI_HOUR
    • SQL_TSI_DAY
    • SQL_TSI_WEEK
    • SQL_TSI_MONTH
    • SQL_TSI_QUARTER
    • SQL_TSI_YEAR

    For you something like

    AVG(TIMESTAMPDIFF(SQL_TSI_DAY, "YourSubjectArea"."1st Sourcing", "YourSubjectArea"."1st Candidate at Review"))

  • Randy Walker
    Randy Walker Rank 1 - Community Starter

    Hi Christian,

    Thank you very much for responding. I am actually using the TIMESTAMPDIFF function in my formulas.

    I am just struggling to find the actual field to compare with.

    E.g. for this measure Average Time from Date Created to 1st Candidate at Interview - I can find the "REQ Date Created" and can use that but I can't find in the subject area "1st Candidate at Review", not sure how to find the date the 1st candidate was moved to review step/status.

    I appreciate your help and feedback.

    Regards,

    Randy

  • [Deleted User]
    edited March 2024

    Are you talking about one of the Fusion products? Because OBI, by definition, is an analytical platform solution which doesn't come with content. So no prebuilt data model on preconnected sources for example.

    What's your data source? If it's Fusion then there's a specific place for those questions. If it's EBS or Peoplesoft or sth else on-premises then you may need the OBIA forums.