Oracle Communities

Oracle Analytics and AI

Child Item

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.

Find more posts tagged with

Oracle Fusion Analytics

HCM Analytics Content

Fusion HCM Analytics

Accepted answers

Prasad Deshpande

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

All comments

Prasad Deshpande

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

Hi @Prasad Deshpande

Thanks for using oracle Communities.

@Nupur Joshi-Oracle Any inputs ?

Regards,

Arjun

Prasad Deshpande

Query for session Variable creation -

Prasad Deshpande

Thank you!! @Mallikarjuna Kuppauru-Oracle

for responding promptly.

Prasad Deshpande

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

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

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

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/58581#Comment_58581

Very helpful.

Thank you very much @Prasad Deshpande!