The Turnover by Location report uses the Employee Turnover topic to show the head count of employees at the start and end date of the selected time period along with the terminated employee counts, turnover rate, termination reason, and gender of the employee. This report shows data from the last complete calendar quarter by default. The standard turnover rate formula is used in the report:
Total Termination (end of period) / ((Headcount at Start + Headcount at End)/2) * 100.
Note: It's recommended that you add only custom fields to this report. Adding standard fields can affect the data accuracy.
Fields
| Field Name | Group by | Sort Order | Totals |
|---|---|---|---|
| Location | Yes | Ascending | |
| Headcount At Start | |||
| Headcount At End | |||
| Terminations At End | |||
| Turnover Rate | Custom | ||
| Voluntary | |||
| Involuntary | |||
| Reason Not Specified | |||
| Non-Binary | |||
| Female | |||
| Male | |||
| Gender Not Specified |
Custom Fields
Headcount At Start
The Headcount At Start custom field shows the number of employees who had the selected employee status at the start of the period. It uses the following expression:
COUNT(CASE
WHEN @EffectiveStart BETWEEN StatusAtStartCustom.EffectiveStart AND ISNULL(StatusAtStartCustom.EffectiveEnd, DATEADD(YEAR, 50, GETDATE()))
AND StatusAtStartCustom.EmploymentStatusId IS NOT NULL THEN 'Y'
ELSE NULL
END)COUNT(CASE
WHEN @EffectiveStart BETWEEN StatusAtStartCustom.EffectiveStart AND
ISNULL(StatusAtStartCustom.EffectiveEnd, DATEADD(YEAR, 50, GETDATE())) AND @EffectiveStart >= CONVERT(date, [EmployeeWorkAssignment].[EffectiveStart]) AND StatusAtStartCustom.EmploymentStatusId IS NOT NULL THEN 'Y' ELSE NULL END)
Headcount At End
The Headcount At End custom field shows the number of employees who had the selected employment status at the end of the period. It uses the following expression:
COUNT(CASE
WHEN @EffectiveEnd BETWEEN StatusAtEndCustom.EffectiveStart AND ISNULL(StatusAtEndCustom.EffectiveEnd, DATEADD(YEAR, 50, GETDATE()))
AND StatusAtEndCustom.EmploymentStatusId IS NOT NULL THEN 'Y'
ELSE NULL
END)
Terminations At End
The Terminations At End custom field shows the number of terminated employees at the end of the period. It uses the following expression:
COUNT(CASE
WHEN @EffectiveEnd BETWEEN TerminationAtEndCustom.EffectiveStart AND ISNULL(TerminationAtEndCustom.EffectiveEnd, DATEADD(YEAR, 50, GETDATE()))
AND TerminationAtEndCustom.EmploymentStatusId IS NOT NULL THEN 'Y'
ELSE NULL
END)
Turnover Rate
The Turnover Rate custom field shows the turnover rate. It uses the following expression:
ISNULL(CONVERT(DECIMAL(19, 2), ROUND((COUNT(CASE WHEN @EffectiveEnd BETWEEN TerminationAtEndCustom.EffectiveStart AND ISNULL(TerminationAtEndCustom.EffectiveEnd, DATEADD(YEAR, 50, GETDATE())) AND TerminationAtEndCustom.EmploymentStatusId IS NOT NULL THEN 'Y' ELSE NULL END) / (NULLIF((COUNT(CASE WHEN @EffectiveStart BETWEEN StatusAtStartCustom.EffectiveStart AND ISNULL(StatusAtStartCustom.EffectiveEnd, DATEADD(YEAR, 50, GETDATE())) AND StatusAtStartCustom.EmploymentStatusId IS NOT NULL THEN 'Y' ELSE NULL END) + COUNT(CASE WHEN @EffectiveEnd BETWEEN StatusAtEndCustom.EffectiveStart AND ISNULL(StatusAtEndCustom.EffectiveEnd, DATEADD(YEAR, 50, GETDATE())) AND StatusAtEndCustom.EmploymentStatusId IS NOT NULL THEN 'Y' ELSE NULL END)) / 2.0, 0.0))) * 100.0, 2)), 0.0)
Voluntary
The Voluntary custom field shows the number of employees who were terminated with a voluntary reason. It uses the following expression:
COUNT(CASE WHEN [TerminationAtEndCustom].EmploymentStatusId IS NOT NULL AND EmploymentStatusReason.IsVoluntaryReason = 1 Then 'Y' ELSE NULL END)
Involuntary
The Involuntary custom field shows the number of employees who were terminated with an involuntary reason. It uses the following expression:
COUNT(CASE WHEN [TerminationAtEndCustom].EmploymentStatusId IS NOT NULL AND EmploymentStatusReason.IsVoluntaryReason = 0 Then 'Y' ELSE NULL END)
Reason Not Specified
The Reason Not Specified custom field shows who was terminated without a specified reason. It uses the following expression:
COUNT(CASE WHEN [TerminationAtEndCustom].EmploymentStatusId IS NOT NULL AND EmploymentStatusReason.IsVoluntaryReason IS NULL Then 'Y' ELSE NULL END)
Non-Binary
The Non-Binary custom field shows the number of non-binary employees who were terminated. It uses the following expression:
COUNT( CASE WHEN ( ([TerminationAtEndCustom].EmploymentStatusId IS NOT NULL AND TerminationAtEndCustom.EffectiveEnd2 IS NULL) AND (CASE WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%(___: __)%' THEN CASE WHEN EmployeeGenderIdentity.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%FEMALE (___: __)%' THEN 'Females' WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%MALE (___: __)%' THEN 'Males' ELSE 'Non-Binary' END WHEN EmployeeAssignedSex.ReferenceCode LIKE '%(___: __)%' THEN CASE WHEN EmployeeAssignedSex.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%F (___: __)%' THEN 'Females' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%M (___: __)%' THEN 'Males' ELSE 'Non-Binary' END ELSE CASE WHEN EmployeeAssignedSex.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%F (%' THEN 'Females' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%M (%' THEN 'Males' ELSE 'Non-Binary' END END) = 'Non-Binary') THEN 'Y' ELSE NULL END)
Female
The Female custom field shows the number of female employees who were terminated. It uses the following expression:
COUNT( CASE WHEN ( ([TerminationAtEndCustom].EmploymentStatusId IS NOT NULL AND TerminationAtEndCustom.EffectiveEnd2 IS NULL) AND (CASE WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%(___: __)%' THEN CASE WHEN EmployeeGenderIdentity.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%FEMALE (___: __)%' THEN 'Females' WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%MALE (___: __)%' THEN 'Males' ELSE 'Non-Binary' END WHEN EmployeeAssignedSex.ReferenceCode LIKE '%(___: __)%' THEN CASE WHEN EmployeeAssignedSex.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%F (___: __)%' THEN 'Females' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%M (___: __)%' THEN 'Males' ELSE 'Non-Binary' END ELSE CASE WHEN EmployeeAssignedSex.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%F (%' THEN 'Females' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%M (%' THEN 'Males' ELSE 'Non-Binary' END END) = 'Females') THEN 'Y' ELSE NULL END)
Male
The Male custom field shows the number of male employees who were terminated. It uses the following expression:
COUNT( CASE WHEN ( ([TerminationAtEndCustom].EmploymentStatusId IS NOT NULL AND TerminationAtEndCustom.EffectiveEnd2 IS NULL) AND (CASE WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%(___: __)%' THEN CASE WHEN EmployeeGenderIdentity.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%FEMALE (___: __)%' THEN 'Females' WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%MALE (___: __)%' THEN 'Males' ELSE 'Non-Binary' END WHEN EmployeeAssignedSex.ReferenceCode LIKE '%(___: __)%' THEN CASE WHEN EmployeeAssignedSex.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%F (___: __)%' THEN 'Females' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%M (___: __)%' THEN 'Males' ELSE 'Non-Binary' END ELSE CASE WHEN EmployeeAssignedSex.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%F (%' THEN 'Females' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%M (%' THEN 'Males' ELSE 'Non-Binary' END END) = 'Males') THEN 'Y' ELSE NULL END)
Gender Not Specified
The Gender Not Specified custom field shows the number of terminated employees who don’t have a specified gender. It uses the following expression:
COUNT( CASE WHEN ( ([TerminationAtEndCustom].EmploymentStatusId IS NOT NULL AND TerminationAtEndCustom.EffectiveEnd2 IS NULL) AND (CASE WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%(___: __)%' THEN CASE WHEN EmployeeGenderIdentity.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%FEMALE (___: __)%' THEN 'Females' WHEN EmployeeGenderIdentity.ReferenceCode LIKE '%MALE (___: __)%' THEN 'Males' ELSE 'Non-Binary' END WHEN EmployeeAssignedSex.ReferenceCode LIKE '%(___: __)%' THEN CASE WHEN EmployeeAssignedSex.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%F (___: __)%' THEN 'Females' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%M (___: __)%' THEN 'Males' ELSE 'Non-Binary' END ELSE CASE WHEN EmployeeAssignedSex.ReferenceCode IS NULL THEN 'Undefined' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%F (%' THEN 'Females' WHEN EmployeeAssignedSex.ReferenceCode LIKE '%M (%' THEN 'Males' ELSE 'Non-Binary' END END) = 'Undefined') THEN 'Y' ELSE NULL END)
Filters
This template includes the following filters: