Department attendance analysis case

1. Overview

1.1 Background

Li Mingming is a personnel. One day, the boss told her to use the company's attendance data to see the attendance of various departments.

After communication, I understand that what the boss needs is not a list of late arrivals and early departures. The boss hopes to understand the management and work conditions of various departments through the analysis of attendance data, so as to obtain reliable suggestions for job setting and quota.

1.2 Analysis ideas

Li Mingming analyzed each department from "lateness", "working hours", and "leave status", as shown below:

1.3 Analysis results

The average working time of the R&D group is the longest, but the lateness rate is the highest (whether late is only a reference item). The company's R&D positions have a heavy business volume. The company's new R&D tasks need to be recruited for new R&D positions.

The average working hours of the finance group is the least, so you need to check the personnel saturation, and do not consider adding new positions in the finance group in the near future;

1.4 Get data

Sample data used in this article: Attendance data.xlsx

The user can click "Department Attendance Analysis" and save it as, and can view the operation mode in the dashboard and the data table used by the dashboard for learning.

2. Implementation

2.1 Processing data

The first step when we get the data, we need to process the raw data. Upload data to FineBI, as shown in the figure below:

2.1.1 Analyze punch-in data

1) Check all the fields of "Attendance data" and add "Group Summary". Find the earliest time and latest time for the user to punch in every day, as shown below:

"Earliest time" corresponds to the employee's working hours.

"Latest time" corresponds to the employee's off-duty time.

2) Filter out the data of non-holidays and non-leaves, that is, the data when the "Status" field is empty. In this part of the data, users should punch in normally. We filter it out to analyze their punch-in data, as shown below:

3) Create a new indicator "Late Judgment Time". In Li Mingming's company, arriving at the company after 9:30 is considered late. So we create a new 9:30 indicator for judgment, as shown below:

4) Compare and judge the attendance time. When the working time is less than six hours, it is judged to be absent; when the "Earliest time" is greater than 9:30, it is judged to be late. The rest are judged as "Full attendance".

5) Hide two fields that we don't need later. As shown in the figure below, just uncheck the field settings.

6) Save and update the self-service dateset.

2.1.2 Collect attendance data

1) Create a new self-service dateset "Attendance summary" and check all fields in the Excel dateset "Attendance data". Add group summary, as shown in the figure below:

2) Filter out all the leave data (note not to select the public holiday data), as shown below.

Because only the data of working days are counted, the public holiday data is not selected here.

3) Combine the leave data and the normal punch-in data of section 2.1.1 into one table. Merge up and down, as shown in the figure below:

4) Calculate working hours. Use the function DATESUBDATEto calculate the time difference between "Latest time" and "Earliest time", as shown below:

5) Save and update the self-service dateset.

2.2 Create analysis components

Use the "Attendance summary" created in Section 2.1.2 to create a dashboard, drag into the "Department, working hours" field, and average the working hours. The following figure shows the average working hours of each person in each department, and you can visually check the workload intensity of each department.

In addition, you can continue to analyze the "lateness, leave, absence, etc." of each department, so that you can understand more detailed attendance status. Help personnel understand the department's attendance management situation.

2.3 Effect view

Please refer to section 1.2 of this article.