I want to report on headcount and FTE (also by age, grade etc.) for each department over the past 10 or so reporting/fiscal quarters. I am running an Analysis in OTBI, using the subject area: Workforce Management - Worker Assignment Event Real Time
I have the variables:
- "Department"."Department Name" (filtered to a single department for testing but will be prompted in a dashboard),
- Reporting quarter (I am unable to save this in a subject area so when I reference it I must use the fomrula I used to calculate it from Time.Date i.e.: CASE WHEN MONTH("Time"."Date") IN (4,5,6) THEN (CASE WHEN MONTH("Time"."Date") >= 4 THEN CAST(YEAR("Time"."Date") AS CHAR) || '/' || CAST(YEAR("Time"."Date")+1 AS CHAR) ELSE CAST(YEAR("Time"."Date")-1 AS CHAR) || '/' || CAST(YEAR("Time"."Date") AS CHAR) END) || ' Q1' WHEN MONTH("Time"."Date") IN (7,8,9) THEN (CASE WHEN MONTH("Time"."Date") >= 4 THEN CAST(YEAR("Time"."Date") AS CHAR) || '/' || CAST(YEAR("Time"."Date")+1 AS CHAR) ELSE CAST(YEAR("Time"."Date")-1 AS CHAR) || '/' || CAST(YEAR("Time"."Date") AS CHAR) END) || ' Q2' WHEN MONTH("Time"."Date") IN (10,11,12) THEN (CASE WHEN MONTH("Time"."Date") >= 4 THEN CAST(YEAR("Time"."Date") AS CHAR) || '/' || CAST(YEAR("Time"."Date")+1 AS CHAR) ELSE CAST(YEAR("Time"."Date")-1 AS CHAR) || '/' || CAST(YEAR("Time"."Date") AS CHAR) END) || ' Q3' WHEN MONTH("Time"."Date") IN (1,2,3) THEN (CASE WHEN MONTH("Time"."Date") >= 4 THEN CAST(YEAR("Time"."Date")-1 AS CHAR) || '/' || CAST(YEAR("Time"."Date") AS CHAR) ELSE CAST(YEAR("Time"."Date")-1 AS CHAR) || '/' || CAST(YEAR("Time"."Date") AS CHAR) END) || ' Q4' END
- "Time"."Calendar Quarter End Date" - after some testing I found this is the same as reporting quarter end date and much simpler to use
- "Headcount" - calculated using the formula - count(distinct "Worker"."Person Number")
- FTE - which has also been calculated from Worker.Normal Hours
I have filtered by:
- Assignment status = Active - Payroll Eligible,
- Department name = X,
- "Assignment Event Details"."Effective Start Date" < = "Time"."Calendar Quarter End Date"
- "Assignment event Details"."Effective End Date">= "Time"."Calendar Quarter End Date"
- "Worker"."Termination Date" is null or >= "Time"."Calendar Quarter End Date".
The returned headcount per quarter is too low and jumps around as it is only counting when there is an assignment change i.e. not all employees active in that quarter. BUT if I input static dates into the filters e.g. replace Calendar Quarter End Date with "30-06-2025" (and remove any time variables from my results) I get the correct numbers. I'm guessing this means there is an issue with the time-event join?
Please help!
