Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

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

Time To Find Calculation

51
Views
2
Comments
Kim Welker
Kim Welker Rank 3 - Community Apprentice
in Oracle Transactional Business Intelligence

Summary

Time To Find Calculation - need Days on Hold metric by Candidate!

Content

Hi - I'm hoping to get some help on an issue that we have had for several years now.

Currently we calculate Time to Fill by subtracting the Latest Filled Date MINUS the Req. Creation Date MINUS the Req. Days on Hold (DOH).  The DOH piece is important to us b/c there are many cases when req's are placed On Hold for a significant length of time which should not reflect poorly on our Recruiters.

In addition, we want to create a Time To Find metric which would essentially show how long it takes from the time that a candidate's application is completed until they move to some advanced step (such as Mgr Review).  Our issue here is that we do not know of a way to calculate the DOH relative to JUST that candidate's recruiting cycle. Not only is the metric inaccurate without consideration of this information, but it also can make Time to Fill look shorter than the Time To Find metric.

How are others handling this??  Could REALLY use your help!

Kim

Comments

  • Jagadish Angadi-29558
    Jagadish Angadi-29558 Rank 3 - Community Apprentice

    Hi Kim,

    This is the formula I developed for my Time in Process calculations. I had some UDF fields too in the below formula which may not work for you hence I have taken them out. Try and see if this works for you also (if same fields are available it will work else you need to change the fields). This formula has all you want, Hold time + Withdrawal time + step completed formula (the last bold part). Let me know if this works for you. Good Luck!!

    IFNULL(EVALUATE_AGGR('MAX(CASE WHEN %1 = ''Application Review'' AND %2 IN (''Hold at Application Review'') THEN %3 END)', "Submission CSW Status - Historical"."Historical Step Name", "Submission CSW Status - Historical"."Historical Status Name", cast(timestampdiff(SQL_TSI_HOUR, "Submission CSW Status - Historical"."Submission Historical Status Start Date", "Submission CSW Status - Historical"."Submission Historical Status End Date") AS DOUBLE) / 24 BY "Recruiting"."Candidate Identification"."Candidate Identifier", IFNULL("Requisition Identification"."Req. Identifier")),0.0) + IFNULL(Cast (timestampdiff(SQL_TSI_HOUR, EVALUATE_AGGR('MIN(CASE WHEN %1 = ''Application Review'' AND %2 IN (''Applicant Withdrew - Application Review'', ''Application Review - Withdrew'', ''Application Review - Applicant Withdrew'') THEN %3 END)', "Submission CSW Status - Historical"."Historical Step Name", "Submission CSW Status - Historical"."Historical Status Name", "Submission CSW Status - Historical"."Submission Historical Status Start Date" BY "Recruiting"."Candidate Identification"."Candidate Identifier", IFNULL("Requisition Identification"."Req. Identifier")),EVALUATE_AGGR('MIN(CASE WHEN %1 = ''Application Review'' AND %2 IN (''Passed Application Review'', ''Rejected - Application Review'', ''Application Review - Rejected'') THEN %3 END)', "Submission CSW Status - Historical"."Historical Step Name", "Submission CSW Status - Historical"."Historical Status Name", "Submission CSW Status - Historical"."Submission Historical Status Start Date" BY "Recruiting"."Candidate Identification"."Candidate Identifier", IFNULL("Requisition Identification"."Req. Identifier")))as double)/24,0.0)

     

    Thanks

    Jagadish

  • Jagadish Angadi-29558
    Jagadish Angadi-29558 Rank 3 - Community Apprentice

    Just take out this part

    IFNULL("Requisition Identification"."Req. Identifier"))),0.0)

    should be look like below, or just play around with it.

    EVALUATE_AGGR('MIN(CASE WHEN %1 = ''Application Review'' AND %2 IN (''Passed Application Review'', ''Rejected - Application Review'', ''Application Review - Rejected'') THEN %3 END)', "Submission CSW Status - Historical"."Historical Step Name", "Submission CSW Status - Historical"."Historical Status Name", "Submission CSW Status - Historical"."Submission Historical Status Start Date" BY "Recruiting"."Candidate Identification"."Candidate Identifier")/24