Data Processing and Analysis

During the base table processing, the data tables connected to FineBI have been cleaned basically.

The problems to be solved subsequently are as follows:

• Only employees on leave have their attendance status marked, while the attendance status of employees who have clocked in/out cannot be distinguished.

• The basic information, such as the name, age, and gender of each employee, is unavailable in the Punch Info table, but available in the Employee Info table.

• The data shows only punch time but lacks related details, for example, working hours.

You need to solve the problems by editing data in My Analysis.

Marking Data with the Punch Behavior

1. Create an analysis subject and upload the Punch Info table from Public Data.

Double-click the table name and rename it Punch Info (Without Leave).

2. Obtain the empty values of the Status field by filter. The obtained data, specifying the employees (neither on holiday nor on leave) should clock in and out normally but do not, can facilitate the analysis of their punch time, as shown in the following figure.

For details, see Filter.

3. Click Group Summary and calculate the earliest and latest punch time separately, as shown in the following figure.

The earliest punch time corresponds to the clock-in time of each employee.

The latest punch time corresponds to the clock-out time of each employee.

For details, see Group Summary.

4. Create a field named Late-or-Not Deadline by using the formula TODATE(CONCATENATE(LEFT(Date,10)," ","09:30:00")), as shown in the following figure. In the sample company, employees arriving at the company later than 9:30 AM are considered late. Therefore, you can create the field Late-or-Not Deadline to determine whether employees are late or not.

The following table explains each part of the above formula.

Additionally, choose More > Time Interval and configure the settings shown in the following figure. If the time interval is smaller than zero, the employee is considered late.

For details, see Time Interval.

5. Compare and judge the punch time.

When the work hours are less than six hours, the employee is considered as Absent.

When the clock-in time is later than 9:30 AM, the employee is considered as Late. Otherwise, the employee is considered as Fully Attendant.

After calculating the working hours, add a new column with the formula IF(Work Hours<6,"Absent",IF(Late-or-Not Deadline<0,"Late","Fullly Attendant")), as shown in the following figure.

For details, see Adding a Formula Column.

6. Click Field Settings and deselect the fields that you do not need later to hide them, as shown in the following figure.

For details, see Field Settings.

7. Save and update the table.

Summarizing the Attendance Data

In this case, you need to summarize the data on employees on leave and those not on leave.

1. Make a copy of the Punch Info table in the analysis subject and modify the name of the table obtained to Attendance Summary in the local project.

2. Obtain all leave data (excluding the data on holidays and compensation leave) by filter, as shown in the following figure.

Note:

Since only the data on working days are selected, the data on holidays and compensation leave are not selected.

3. Click Group Summary and configure the same setting as that for Punch Time (Without Leave), as shown in the following figure.

Note:

The procedure is to prepare for merging the data in section "Marking Data with the Punch Behavior."

4. Merge the leave data and the data on normal punch in section "Marking Data with the Punch Behavior" into a table through Union All, as shown in the following figure.

For details, see Union All.

After the attendance data has been organized, you may want to add basic information, such as employees' names, ages, and genders, to the punch information. The above basic information is all available in the Employee Info table.

Therefore, you need to use the Join function to merge the data from the two tables based on the Employee ID field. Since you need to retain all the data from the Punch Info table, you need to select Left Join, as shown in the following figure.

For details, see Join.

Since only the punch time is available, you need to continue using the dataset for data processing if you want to know the working hours of each employee.

1. Use the DATESUBDATE function to calculate the time interval between Latest Time and Earliest Time. Enter DATESUBDATE(Latest Time,Earliest Time,"m")/60, as shown in the following figure.

If you use real-time data, you can use Time Interval to calculate the working hours, as shown in the following figure.

2. Use each employee's ID number obtained after merging the data and calculate the employee's age by the formula YEAR() - TOINTEGER(MID(ID Number,7,4)), as shown in the following figure.

At this moment, the data processing is completed. Of course, you can use field settings to remove any unnecessary intermediate fields.