Ethnicity causing Workforce Trend SA to produce inaccurate headcount

Andrew Mc

ethnicity workforce trends 2.catalog

When adding ethnicity to the Workforce Trend SA it causes the Period Start and End headcount to double/triple for those that have selected two or more races.

I have attached a sample report with the custom field I'm using to group the team member's ethnicity. But I'm having difficulty with getting the headcounts to work as expected. Has anyone else had this issue and what was your work around? I need to use the period active start and end dates fields for this report as I'm working out the average actives for a 12 month period.

When adding the Ethnicity Field:

When adding the custom field, the value is showing 3, expected value would be 1:

Only when I remove ethnicity entirely from the report is when it correctly displays:

Andrew Mc

Not sure if it was the best approach, but I was able to get this working by dividing the headcount by the count of ethnicities listed when more than one is selected. I provided a sample below:

It appears to be working where those showing as 'Two or More' will have a headcount of 1 vs 2+. As I said, I'm not sure if it was the best approach, but this work around seems like it is working for me.

CASE WHEN COUNT(DISTINCT "Worker"."Employee Ethnicity Code" by "Organization Hierarchy"."Level 02 Organization Name", "Worker"."Person Number") > 1

THEN "Workforce Trend"."Period Start Active Headcount" / COUNT(DISTINCT "Worker"."Employee Ethnicity Code" by "Organization Hierarchy"."Level 02 Organization Name", "Worker"."Person Number") 

ELSE "Workforce Trend"."Period Start Active Headcount"

END