How to create a measure in OBIEE

Question

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

[Deleted User] · Answer

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.

Randy Walker · Answer

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] · Answer

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"))