Summary
One definition of the “Employee Turnover” is the percentage of employees that left an organization during a given time period. Organizations typically calculate turnover rates annually or quarterly. Employee turnover is a crucial metric for measuring the attractiveness of a company and the performance of human resources departments. This blog post provides a step-by-step description how such a story report can be created.
Introduction
There are several slightly different ways to calculate the “Employee Turnover” of a company or any organizational unit. One simple Turnover metrics is the quotient of the “Number of Employee Terminations” divided by the “Total Headcount” while the “Total Headcount” is considered as { “Employee at Start of a Period” plus “Employees at the end of a period” } divided by 2.
For an example, see Figure 1 below: Organizational Unit “BestRun Australia” had 62 employees one year back. During the last 12 months, 3 employees left the organizational unit and 17 newcomers joined, taking the final headcount to 76. Then, the “Average Number of Employees” is 69 (= (62 + 76) / 2) and the “Annual Employee Turnover Rate” is determined as 4.3% (= 3 / 69). This employee turnover rate can be compared with similar companies to determine where the human resource management stands.
The blogpost mainly focusses on the technical aspects how to create such a story report most effectively. Of course, it is easily possible to enhance the presented approach by further aspects, e.g.
In the following the technical design of the data source and the story is described.
Underlying design idea of the Report
The challenge of the design for this report is to derive (1) the headcount at two different point in times (i.e. ‘today’ and ‘one year back’) and (2) simultaneously the terminations within a date range. The approach presented here achieves that with a single data source. An alternative approach which uses two (or even three) data sources (i.e. one running for a data range to determine the terminations and one/two for the headcounts relevant for particular days) should be avoided, last but not least due to performance constraints.
To achieve that, the full Job Information history from the last year of all employees is read. Afterwards, the relevance for “Headcount One Year Back”, “Termination” and “Headcount” is checked and a counter is derived.
Example (see Figure 2): Employee “William Dart” has one effective dated time slice from Jan 01, 2017 until Dec 31, 9999 (indicated in red). This time slice contributes to the ‘Headcount Today’ and ‘Headcount One Year Back’. Employee “Barry Brendon” has 2 time slices relevant for the last year (one indicted in violet and one in yellow). While the first time slice contributes to ‘Headcount One Year Back’, the 2nd time slice contributes to ‘Headcount Today’.
Technical design of the Query
The data source for the creation of the turnover report is indicated at Figure 3. A Time Range Filter is applied to “Job Information” (for ‘Personal Information’ and ‘Event Reason’ leave the default time filter):
Select a comprehensive set of columns from the three objects, as (1) “First Name” / “Last Name” of “Personal Information”, (2) “Effective Start Date”, “Effective End Date”, “Employment Status”, “User ID” and “Legal Entity” (if required additional “organizational units”) from “Job Information” and (3) “Event” from “Event Reason”.
Create a calculated column “TERMINATIONCOUNTER” with label “Termination Counter”: IF([Foundation Objects#Event Reason#Event]="internal picklist ID for Termination",1, 0 )
Technical design of the Story
Following 2 calculated measures of type “Date Difference” are needed:
Following 3 calculated measures of type “Calculated Measure” are needed:
Following 2 calculated measures of type “Restricted Measure” need to be created:
Following 3 calculated measures of type “Aggregation” are needed:
Finally create an additional “Calculated Measure” Turnover Rate:
IF(ISNULL([#Headcount Today]+[#Headcount One Year Back]), 0, -[#Terminations]/0.5/([#Headcount Today]+[#Headcount One Year Back]) )
Now it is possible to create charts based on this query. The charts from Figure 1 are:
One definition of the “Employee Turnover” is the percentage of employees that left an organization during a given time period. Organizations typically calculate turnover rates annually or quarterly. Employee turnover is a crucial metric for measuring the attractiveness of a company and the performance of human resources departments. This blog post provides a step-by-step description how such a story report can be created.
Introduction
There are several slightly different ways to calculate the “Employee Turnover” of a company or any organizational unit. One simple Turnover metrics is the quotient of the “Number of Employee Terminations” divided by the “Total Headcount” while the “Total Headcount” is considered as { “Employee at Start of a Period” plus “Employees at the end of a period” } divided by 2.
For an example, see Figure 1 below: Organizational Unit “BestRun Australia” had 62 employees one year back. During the last 12 months, 3 employees left the organizational unit and 17 newcomers joined, taking the final headcount to 76. Then, the “Average Number of Employees” is 69 (= (62 + 76) / 2) and the “Annual Employee Turnover Rate” is determined as 4.3% (= 3 / 69). This employee turnover rate can be compared with similar companies to determine where the human resource management stands.
Figure 1 – Turnover Rate in Percentage shown per Legal Entity (left) and respective “Headcount One Year Back”, “Terminations” and “Headcount Today” visualized with a waterfall chart which uses the dimension Legal Entity (right)
The blogpost mainly focusses on the technical aspects how to create such a story report most effectively. Of course, it is easily possible to enhance the presented approach by further aspects, e.g.
- To consider both, involuntary and voluntary turnover rates
- Top Talent turnover rate
- Turnover rate by additional dimensions like Department, Manager, Position Level, Pay Grade, Gender, Age Groups, Length of Service and many more.
- Retention Rate, defined as {100 – ‘Turnover Rate’}
- New Hire Retention Rate
- More flexibility regarding the considered time interval (e.g. quarterly rate instead of annual rate)
In the following the technical design of the data source and the story is described.
Underlying design idea of the Report
The challenge of the design for this report is to derive (1) the headcount at two different point in times (i.e. ‘today’ and ‘one year back’) and (2) simultaneously the terminations within a date range. The approach presented here achieves that with a single data source. An alternative approach which uses two (or even three) data sources (i.e. one running for a data range to determine the terminations and one/two for the headcounts relevant for particular days) should be avoided, last but not least due to performance constraints.
To achieve that, the full Job Information history from the last year of all employees is read. Afterwards, the relevance for “Headcount One Year Back”, “Termination” and “Headcount” is checked and a counter is derived.
Example (see Figure 2): Employee “William Dart” has one effective dated time slice from Jan 01, 2017 until Dec 31, 9999 (indicated in red). This time slice contributes to the ‘Headcount Today’ and ‘Headcount One Year Back’. Employee “Barry Brendon” has 2 time slices relevant for the last year (one indicted in violet and one in yellow). While the first time slice contributes to ‘Headcount One Year Back’, the 2nd time slice contributes to ‘Headcount Today’.
Figure 2 Example: How job info time slices of two employees contribute to ‘Headcount Today’ and ‘Headcount One Year Back’
Technical design of the Query
The data source for the creation of the turnover report is indicated at Figure 3. A Time Range Filter is applied to “Job Information” (for ‘Personal Information’ and ‘Event Reason’ leave the default time filter):
- ‘Granularity’ = “Day” with ‘Range Extension’ = “365 Days Back”
- Under ‘Records Returned’ select “Include any records that partially or completely overlap your defined date range”
Figure 3 Query to generate the employee turnover
Select a comprehensive set of columns from the three objects, as (1) “First Name” / “Last Name” of “Personal Information”, (2) “Effective Start Date”, “Effective End Date”, “Employment Status”, “User ID” and “Legal Entity” (if required additional “organizational units”) from “Job Information” and (3) “Event” from “Event Reason”.
Create a calculated column “TERMINATIONCOUNTER” with label “Termination Counter”: IF([Foundation Objects#Event Reason#Event]="internal picklist ID for Termination",1, 0 )
Technical design of the Story
Following 2 calculated measures of type “Date Difference” are needed:
- End Date -> Today (in Days): Result Granularity “Day” with Time (A) as “Effective End Date” and Time (B) as “Current Date”
- Start Date -> Today (in Days): Result Granularity “Day” with Time (A) as “Effective Start Date” and Time (B) as “Current Date”
Following 3 calculated measures of type “Calculated Measure” are needed:
- Time Slice Counter (Today) helper: IF([#Start Date -> Today (in Days)]<0 and [#End Date -> Today (in Days)]>0 ,1 ,0 )
- Time Slice Counter (One Year Back) helper: IF([#Start Date -> Today (in Days)]<=-365 and [#End Date -> Today (in Days)]>=(-365+1) ,1 ,0 )
- Terminations – Helper: IF([#Start Date -> Today (in Days)]>=-(365-1) and[#Start Date -> Today (in Days)] <0, -["Query Name": TERMINATIONCOUNTER], 0 )
Following 2 calculated measures of type “Restricted Measure” need to be created:
- Time Slice Counter (Today) which uses “Time Slice Counter (Today) helper” as Measure and Restriction for “Employment status” not equal to “Retired/Terminated”
- Time Slice Counter (One Year Back) which uses “Time Slice Counter (One Year Back) helper” as Measure and Restriction for “Employment status” not equal to “Retired/Terminated”
Following 3 calculated measures of type “Aggregation” are needed:
- Headcount Today which uses “Time Slice Counter (Today)” as Measure and Operation = “SUM”. As “Aggregation Dimensions” the values “User ID”, “Effective Start Date” and “Transaction Sequence Number” are used.
- Headcount One Year Back which uses “Time Slice Counter (One Year Back)” as Measure and Operation = “SUM”. As “Aggregation Dimensions” the values “User ID”, “Effective Start Date” and “Transaction Sequence Number” are used.
- Terminations which uses “Terminations - Helper” as Measure and Operation = “SUM”. As “Aggregation Dimensions” the values “Legal Entity”, “User ID” and “Effective Start Date” are used.
Finally create an additional “Calculated Measure” Turnover Rate:
IF(ISNULL([#Headcount Today]+[#Headcount One Year Back]), 0, -[#Terminations]/0.5/([#Headcount Today]+[#Headcount One Year Back]) )
Now it is possible to create charts based on this query. The charts from Figure 1 are:
- Chart type “Bar/Column” with Measure “Turnover Rate”, Dimension “Legal Entity” (left part of Figure 1)
- Chart Type “Waterfall” with Measures “Headcount One Year Back”, “Terminations” and “Headcount Today” and Dimension “Legal Entity” (right part of Figure 2)
- SAP Managed Tags:
4 Comments
| User | Count |
|---|---|
| 5 | |
| 3 | |
| 2 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
