Resignation Analysis Report

1. Overview

1.1 Background

Xiao Xia, a human resources student, found that the number of resignees in the company seems to have increased recently, but there was no statistical analysis of resignation before. Xiao Xia did not have sufficient grasp of the overall trend of resignation of company employees. She needed to make a data analysis dashboard for resignation. She understands the overall trend.

1.2 Analysis process

The resignation rate formula used by Xiaoxia: resignation rate = (number of people who leave during the period)/(number of people who are on the job at the end of the period + the number of people who leave during the period)

That is, resignation rate in 2018 = the number of resignees in 2018/the number of employees at the end of 2018 + the number of resignees during the period. The resignation is analyzed from four aspects: age, department, rank, and education level. (If the user has relevant data such as entry period, salary, etc., it can be analyzed from more aspects).

1.3 Analysis results

The total resignation rate of the company in 2020 is 27.65%, which is higher than the 15% resignation rate of other companies in the same industry, and higher than the resignation rate of the company in the previous two years. Follow-up analysis of the reasons for resignation filled in by employees is required to investigate the reasons for the high resignation rate.

The company's senior management should have been relatively stable, but the resignation rate of P7 and P6 was 30%. The decision-making team frequently changed coaches, which required special attention.

1.4 Get the dashboard

Sample data used in this article: personnel statistics table.xlsx

Click to view the dashboard: resignation analysis report. After saving the dashboard, users can view the detailed operations in the learning dashboard.

2. Implementation

2.1 Prepare data

Upload the "personnel statistics table" to FineBI.

2.1.1 Filter out the date of resignation

1) Use the "personnel statistics table" to create a self-service dataset without selecting the "Entry date", as shown in the figure below:

2) The resignation date filters out non-empty data, so that all resignees can be filtered out, as shown below:

3) Add a remark column "Resign", as shown in the figure below:

4) Save and update the self-service dataset.

2.1.2 Combine resignation date and entry date into one column

1) Create a new self-service dataset "Entry and Resignation Consolidation Table", uncheck the "Resign date", as shown in the figure below:

2) Add a remark column "Entry", as shown in the figure below:

3) Merge up and down with the "resign info", merge the entry date and resignation into one column as scheduled, and change the merged result to "date", as shown below:

You can use the "remark" field to determine whether the date is the resignation date or the entry date.

2.2 Construct analysis fields

1) Create a new dashboard, drag in a "Year" filter component, and click "OK" directly, as shown below:

2) Use the newly created "Entry and Resignation Consolidation Table" to create a component and enter the component editing interface.

2.2.1 Calculate number of resignees during the period

Copy "Counter" and rename it to "Resignees number during the period". Perform detail filtering, and filter out the data of which remark is "Resign" and "date" is in "All Year" of the filter component, as shown in the following figure:

2.2.2 Calculate the number of employees at the end of the period

Number of employees at the end of the period = cumulative number of employees at the end of the period-cumulative number of resignees at the end of the period.

1) Calculate the cumulative number of employees at the end of the period: copy the "Counter" field, rename it to "cumulative number of employees at the end of the period", and perform detail filtering on it. The filter conditions are shown in the figure below:

When the flter component selects the year 2019, the filter result is all the people who have joined the job at the end of 2019.

2) Calculate the cumulative number of resignees at the end of the period: copy the "Counter" field, rename it to "cumulative resignees at the end of the period", and use detail filtering. The filter conditions are shown in the figure below:

3) Add a calculation indicator: number of employees at the end of the period = cumulative number of employees at the end of the period- cumulative number of resignees at the end of the period, as shown below:

2.2.3 Calculate resignation rate

Resignation rate = Resignees number during the period/(number of employees at the end of the period + Resignees number during the period)

Add a calculation indicator and enter the formula, as shown below:

2.3 Create the component

1) In order not to repeat section 2.2, we copy the component that only builds the analysis fields, as shown in the following figure:

2) Make indicator cards of "Resignation rate, number of employees at the end of the period, Resignees number during the period" respectively (see: KPI indicator card for the production method), as shown in the figure below:

3) For others, you can view specific operations by saving the dashboard in section 1.4.

2.4 Effect view

For details, please refer to section 1.2 of this article.