Need help - New hire Termination Counts -

Prasad Deshpande · Sep 16, 2024 7:53PM

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.

Prasad Deshpande · Sep 17, 2024 12:15PM

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

Prasad Deshpande · Sep 16, 2024 8:06PM

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 · Sep 16, 2024 8:38PM

Hi @Prasad Deshpande

Thanks for using oracle Communities.

@Nupur Joshi-Oracle Any inputs ?

Regards,

Arjun

Prasad Deshpande · Sep 17, 2024 7:07PM

Thank you!! @Mallikarjuna Kuppauru-Oracle

for responding promptly.

Prasad Deshpande · Sep 18, 2024 7:18PM

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

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!