Categories
- All Categories
- 152 Oracle Analytics News
- 28 Oracle Analytics Videos
- 14.8K Oracle Analytics Forums
- 5.7K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 54 Oracle Analytics Trainings
- 12 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
How to create a measure in OBIEE

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
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"))
1 -
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
0 -
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.
0