Categories
Need help - New hire Termination Counts -
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.
Best 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 - 13
Answers
-
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
0 -
0
-
Thank you!! @Mallikarjuna Kuppauru-Oracle
for responding promptly.
1 -
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 - 12. 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!
2 -
@Prasad Deshpande - This is a great use case ,thanks for sharing the code snippet as well.
0 -
Very helpful.
Thank you very much @Prasad Deshpande!
0
