Calculation Usage

When the existing data is insufficient for analysis, new data must be derived through calculation.

The calculation in FineBI can be used in various scenarios such as data segmentation, data type conversion of fields (for example, string-to-date), data aggregation (for adding calculation fields in dashboards only), and ratio calculation.

In Data Processing and Analysis, multiple calculations are used to obtain Working Hours. However, what calculation methods are available and how to choose the right calculation method for different scenarios in FineBI?

This document will introduce four main types of calculation available in FineBI:

• Aggregation calculation

• Detail-level calculation (data source row-level calculation)

• Functions that control the calculation granularity

• Component quick calculation

Detail-level calculation is mainly performed during data editing in My Analysis.

To calculate the daily working hours of each employee, you can perform detail calculation in data editing, as shown in the following figure.

The following table describes the calculation in detail.

Example:

Each order record contains data on the total price and the unit price, and you want to use the data to calculate the total purchase quantity. In this case, you can add a calculation indicator with the formula Total Price/Unit Price, and summarize the purchase quantity for each order to obtain the total purchase quantity during summation.

You can use the example data Contract Fact Table. You can enter the component editing page, click the  icon, select Add Calculation Field from the drop-down list, name the calculation field Unit Price, input Contract Amount/Purchase Quantity, and click OK, as shown in the following figure.

Aggregation calculation is mainly performed during calculation field adding in dashboards.

For example, you may want to calculate the average working hours of all employees.

On the component editing page, you can click the  icon, select Add Calculation Field, input the required formula, and click OK, as shown in the following figure.

Note:

The input formula functions the same as AVG_AGG(Working Hours).

The following figure shows the obtained field.

The formula returns the average working hours through dividing the total daily working hours of all employees by the total number of punches of all employees.

For details, see Calculation Type Selecting.

The key advantage of aggregation calculation lies in the dynamic adaptation to the selected dimension. For example, you may want to display the data dynamically based on different filter conditions in the dashboard, specifically, to display the corresponding data on average working hours of the selected group, as shown in the following figure.

You can switch from KPI Card to Group Table in Chart Type, and drag the Department field into the Dimension bar. In this case, the average working hours of different departments are displayed. If you drag the Employee field into the Dimension bar, the average working hours of different employees are displayed, as shown in the following figure.

Since this is a key advantage that cannot be achieved through calculation during data editing, you are advised to use aggregation calculation for visualization analysis.

Quick calculation encapsulates commonly used aggregation functions to enhance your working efficiency.

For example, to calculate the proportions of various attendance behaviors, you can display all behavior counts in the percentage format, as shown in the following figure.

In this case, the proportions of various attendance behaviors are obtained. You can also use the aggregation calculation to achieve the same effect, as shown in the following figure.

For details, see Quick Calculation Overview.

Count and Conditional Count

You want to calculate the headcount and absence count of each department.

Calculating the Headcount of Each Department

Add a calculation field with the formula COUNTD_AGG(Employee ID), as shown in the following figure.

Drag the corresponding fields into the Dimension and Indicator bars separately, as shown in the following figure.

Conditional Count

To calculate the absence count, a condition must be added to the COUNT_AGG function, as shown in the following figure. The condition is that a record is counted if the value of the Attendance Result is Absence. Otherwise, the record is not counted.

For details, see Conditional Summation and Count.