Oracle Communities

Oracle Analytics and AI

Child Item

How to write calculation: Headcount filtered by Length of Service <= 365 Days

I am trying to write a calculation to provide the Headcount at the end of the period for Team Members with Length of Service <= 365 days. The Headcount will be utilised in a table visual by Enterprise Period (see below). I have written using CURRENT_DATE and realise it is not correct as it is alway using current date, but I need it to calculate service to the end of each period.

Any thoughts on the correct calculation?

Thanks Scott

Find more posts tagged with

Oracle Fusion Analytics

FDI

Fusion HCM Analytics

Accepted answers

JohnW-Oracle

Hi @Scott_Turner_

Based on your screenshot, you are using a Filter Function and this is the best option for performance considerations. You could also use 'Times series' functions Ago and Todate.
You indicated you want to calculate to the end of each period so instead of using Current_Date, you could use: "HCM - Workforce Core"."Facts - Period Start and End"."Headcount (Period End)"

This is described here in the Reference Guide:

https://docs.oracle.com/en/cloud/saas/analytics/24r2/fahia/chapter-subject-areas.html

One other note for the calculation.

"Headcount" requires prerequisite steps for FDI – The headcount column must be updated correctly in the Manage Employment screen of cloud hcm application.
"Worker Count" – No pre-reqs required for this, this is computed in FAW.

Often "Worker Count" is used instead of "Headcount" in calculations and filters.

Hope that helps.

Regards,

John

All comments

JohnW-Oracle

Hi @Scott_Turner_

This is described here in the Reference Guide:

https://docs.oracle.com/en/cloud/saas/analytics/24r2/fahia/chapter-subject-areas.html

One other note for the calculation.

Often "Worker Count" is used instead of "Headcount" in calculations and filters.

Hope that helps.

Regards,

John

Scott_Turner_

Hi @JohnW-Oracle

Thank you for your response, it was spot on see below (Fig 1.), and I was able to calculate the Headcount by Using Enterprise Period End Date.

However, this has provided me with a new issue whilst attempting to write a calculation using PERIODROLLING I can't achieve the desired outcome (Fig 2.).

I was expecting that the PERIODROLLING would add the last 12 months and I would then be able to /12 to obtain the average. However, new calculation is not returning expected value.

Would appreciation your assistance.

Thanks

Scott

JohnW-Oracle

Hi @Scott_Turner_

The PERIODROLLING issue may be a little more complicated and I'm not sure that I understand the issue from the screenshot so you may want to start a new thread on that topic to raise visibility or open a SR.

The 'Headcount' filed can sometimes have some caveats as could the 'Volunteer Termination Headcount'. So you would have to build these calculated columns one small piece at a time and verify the data is as expected.

What is the calculation for the NH - Headcount by LOS column? Is the aggregation method "SUM" for all these?

Regards,

John

Scott_Turner_

Hi @JohnW-Oracle

Thank you for your help I will proceed to raise another question.

Thanks

Scott