Oracle Fusion HCM Analytics

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion HCM Analytics

Categories

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

Need help - New hire Termination Counts -

Accepted answer
71
Views
7
Comments
Prasad Deshpande
Prasad Deshpande Rank 5 - Community Champion
in Oracle Fusion HCM Analytics

We have a requirement -

How many people got hired in last quarter and of those people how many got terminated - New hire Termination Count

Currently, Oracle provides a way using Tenure Service in months for pulling any person terminated with tenure < 3 months.

FILTER("HCM - Workforce Core"."Facts - Workforce Events"."Termination Headcount" USING "HCM - Workforce Core"."HR Action"."Action Type Code"= 'EMPL_TERMINATE' and ( "HCM - Workforce Core"."Months Of Service Band"."Name" = '1-3' or "HCM - Workforce Core"."Months Of Service Band"."Name" ='0' ))

This is a near approximation of what we need but it is not exactly what is desired. Any help on this topic from experts would be appreicated.

Tagged:

Best Answer

  • Prasad Deshpande
    Prasad Deshpande Rank 5 - Community Champion
    Answer ✓

    Query for session Variable creation -

    SELECT DISTINCT H.CAL_QUARTER_START_DATE from DW_QUARTER_D G , DW_QUARTER_D H where G.CAL_QUARTER_CODE in (
    SELECT DISTINCT F.CAL_QUARTER_CODE from DW_QUARTER_D E , DW_QUARTER_D F where E.CAL_QUARTER_CODE in (
    Select DISTINCT D.CAL_QUARTER_CODE from DW_QUARTER_D C , DW_QUARTER_D D where C.CAL_QUARTER_CODE in (
    SELECT DISTINCT B.CAL_QUARTER_CODE FROM DW_QUARTER_D A, DW_QUARTER_D B
    WHERE A.CURRENT_CAL_QTR_CODE='Current' AND B.CAL_QUARTER_END_DATE = A.CAL_QUARTER_START_DATE - 1 )
    AND D.CAL_QUARTER_END_DATE = C.CAL_QUARTER_START_DATE - 1 )
    AND F.CAL_QUARTER_END_DATE = E.CAL_QUARTER_START_DATE - 1 )
    AND H.CAL_QUARTER_END_DATE = G.CAL_QUARTER_START_DATE - 1

Answers

  • Prasad Deshpande
    Prasad Deshpande Rank 5 - Community Champion

    We have same issue on a similar visualization - Retention Rate - Last 4 completed quarters

    Oracle's definition of New hire Termination - How many people got terminated in last 4 completed quarters (rolling) and at the time of termination their tenure of service was less than 1 year
    Divided by Average headcount - (Average headcount- period beginning and period ending for last 4 completed quarters.)

    1 - "HCM - Workforce Core"."Facts - Workforce Attrition"."New Hire Termination Count"/ "HCM - Workforce Core"."Facts - Workforce Events"."New Hire Headcount"

    Our client's requirement - Of those people who got hired in las 4 completed quarters (rolling), how many people are still with the company ?

    Let me know if I need to provide more explanation on this

  • Mallikarjuna Kuppauru-Oracle
    Mallikarjuna Kuppauru-Oracle Rank 8 - Analytics Strategist

    Hi @Prasad Deshpande

    Thanks for using oracle Communities.

    @Nupur Joshi-Oracle Any inputs ?

    Regards,

    Arjun

  • Prasad Deshpande
    Prasad Deshpande Rank 5 - Community Champion

    Thank you!! @Mallikarjuna Kuppauru-Oracle

    for responding promptly.

  • Prasad Deshpande
    Prasad Deshpande Rank 5 - Community Champion

    SOLVED ! I was able to create this logic as below -

    1. Create 2 session variables to Last quarter completed - start date and Last 4th completed quarter start date

    SELECT DISTINCT H.CAL_QUARTER_START_DATE from DW_QUARTER_D G , DW_QUARTER_D H where G.CAL_QUARTER_CODE in (
    SELECT DISTINCT F.CAL_QUARTER_CODE from DW_QUARTER_D E , DW_QUARTER_D F where E.CAL_QUARTER_CODE in (
    Select DISTINCT D.CAL_QUARTER_CODE from DW_QUARTER_D C , DW_QUARTER_D D where C.CAL_QUARTER_CODE in (
    SELECT DISTINCT B.CAL_QUARTER_CODE FROM DW_QUARTER_D A, DW_QUARTER_D B
    WHERE A.CURRENT_CAL_QTR_CODE='Current' AND B.CAL_QUARTER_END_DATE = A.CAL_QUARTER_START_DATE - 1 )
    AND D.CAL_QUARTER_END_DATE = C.CAL_QUARTER_START_DATE - 1 )
    AND F.CAL_QUARTER_END_DATE = E.CAL_QUARTER_START_DATE - 1 )
    AND H.CAL_QUARTER_END_DATE = G.CAL_QUARTER_START_DATE - 1

    2. Create the Key Metric or Custom Data Calculation as below -

    New hire Termination counts - Last Completed Quarter

    FILTER("HCM - Workforce Core"."Facts - Workforce Events"."Termination Headcount" USING "HCM - Workforce Core"."HR Action"."Action Type Code"= 'EMPL_TERMINATE' and "HCM - Workforce Core"."Employment Information"."Work Relationship Start Date" >= VALUEOF(NQ_SESSION.STO_TRAILING_GREGORIAN_QUARTER_START_DATE))

    &

    Retention Rate - Last 4 completed Quarters

    1 - FILTER("HCM - Workforce Core"."Facts - Workforce Events"."Termination Headcount" USING "HCM - Workforce Core"."HR Action"."Action Type Code"= 'EMPL_TERMINATE'
    and "HCM - Workforce Core"."Employment Information"."Work Relationship Start Date" >= VALUEOF(NQ_SESSION.STO_TRAILING_4TH_COMPLETED_GREGORIAN_QUARTER_START_DATE))
    / "HCM - Workforce Core"."Facts - Workforce Events"."New Hire Headcount"

    😀

    Cheers!

  • Nupur Joshi-Oracle
    Nupur Joshi-Oracle Mod

    @Prasad Deshpande - This is a great use case ,thanks for sharing the code snippet as well.

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics Strategist
    https://community.oracle.com/products/oracleanalytics/discussion/comment/58581#Comment_58581

    Very helpful.

    Thank you very much @Prasad Deshpande!