Resignation Analysis Report

Background

You, as an HR manager notice that the number of employees resigned seems to have increased recently. However, the company did not statistically analyze resignation data before. As a result, you lack a comprehensive understanding of the overall resignation trends. You need to create a related data analysis dashboard to help her grasp the broader tendency.

Analysis Process

You can use the formula  = (Number of Employees Resigned in the Period)/(Number of Employees in Service at the Period End + Number of Employees Resigned in the Period).

To be more specific, the formula is Resignation Rate in 2018 = Number of Employees Resigned in 2018/(Number of Employees in Service at the End of 2018 + Number of Employees Resigned in the Period) to analyze resignation by age, department, job title, and education level. (You can analyze resignation from multiple aspects if years of service, salary, and related data are provided.)

Analysis Result

The company's overall resignation rate in 2020 is 27.65%, which is higher than the industry average 15% and also exceeds the resignation rate in the previous two years. Next, you can analyze the resignation reasons provided by employees to identify the underlying causes of the high resignation rate.

Company executives are expected to remain relatively stable. However, the attrition rate for levels P7 and P6 reached 30%, and the decision-making team has experienced frequent personnel changes. This concerning trend demands immediate attention.

Data Access

You can download the sample data Employee Statistics.xlsx.

Data Preparation

1. Create an analysis subject, and upload the sample data Employee Statistics.

2. Filter Resignation Date to obtain non-null data (namely, all resigned employees), as shown in the following figure.

3. Convert Resignation Date and Employment Date columns into a single column in the table to facilitate subsequent analysis of indicators, such as tenure duration of resigned employees, as shown in the following figure. 

4. Click Filter Settings to rename the two added field columns, as shown in the following figure.

5. Click Save and Update.

Creating an Analysis Field

Add a component, enter the component editing page, and add a time parameter, as shown in the following figure.

Calculating the Number of Employees Resigned in the Period

1. Copy Count, and rename it Number of Employees Resigned in the Period, as shown in the following figure.

Add an indicator condition to obtain data where the value of Remark is Resignation Date and the Resignation Date falls within the full year selected in the filter component, as shown in the following figure.

Calculating the Number of Employees in Service at the Period End

Use the formula Number of Employees in Service at the Period End = Cumulative Number of New Employees at the Period End - Cumulative Number of Employees Resigned at the Period End.

1. To calculate Cumulative Number of New Employees at the Period End, copy the Count field, rename it Cumulative Number of New Employees at the Period End, and add an indicator condition. The following figure shows the filter condition.

If you select 2019 in the filter component, the filter result represents the cumulative number of employees by the end of 2019.

2. To calculate Cumulative Number of Employees Resigned at the Period End, copy the Count field, rename it Cumulative Number of Employees Resigned at the Period End, and add an indicator condition. The following figure shows the filter condition.

3. Add a calculation field (Number of Employees in Service at the Period End = Cumulative Number of New Employees at the Period End - Cumulative Number of Employees Resigned at the Period End), as shown in the following figure.

Calculating the Resignation Rate

Add a calculation field named Resignation Rate, and enter the formula Number of Employees Resigned in the Period/(Number of Employees in Service at the Period End + Number of Employees Resigned in the Period), as shown in the following figure.

Creating a Component

1. To avoid repeating the steps in "Creating an Analysis Field", you can duplicate the component that only contain the analysis fields, as shown in the following figure.

2. Create the following KPI cards: Resignation Rate, Number of Employees in Service in the Period, and Number of Employees Resigned in the Period. (For details about how to create KPI cards, see: KPI Card). The following figure shows how to create the KPI card Number of Employees Resigned in the Period. The procedure for creating other KPI cards follows the same logic.

Dashboard Creation

1. Click the Add Dashboard icon, as shown in the following figure.

2. Drag the created components into the dashboard, as shown in the following figure.

3. Drag Year Filter Component into the dashboard, tick Bind Parameter, click, and tick Parameter, as shown in the following figure.

Effect Display

For details, see the section "Analysis Process."