Getting Started with Calculations

This document introduces how to create and use the calculation function with the sample data in FineBI.

Through this document, you will learn what calculations are in, how to create and edit calculation fields, how to use the calculation editor, and how to use calculation fields on the component editing page.

If you are unfamiliar with how to perform calculations or create calculation fields in, this document is very suitable for you to start with.

Calculation fields allow you to create data using data that already exists in the data source.

When you create a calculation field, you are actually creating a field/column (using the data source) whose values or members are controlled by the calculation. This new calculation field is saved to FineBI and can be used to create charts. You can rest assured that the original data will remain unchanged.

There are many reasons for using calculation fields. The following lists some examples:

Splitting data

Converting field data types (for example, converting a string to a date)

Aggregating data (only applicable to adding calculation fields in components)

Filtering results

Calculating proportions

The following sections show scenarios where calculations are needed.

Lack of Needed Data for Analysis in the Data Source

You can download the sample data: Supermarket Sales Data.xlsx.

The data table Supermarket Sales Data contains the fields Sales and Profit, but you want to calculate the cost. In this case, you can calculate the field Cost by using a formula similar to the content shown in the following figure.

Value Conversion in Components

For example, you may want to calculate the cost proportions of different regions. In this case, you can use quick calculations to display the cost proportions of different regions in the component, as shown in the following figure.

Quick Data Categorization

You may want to quickly mark the data in the component.

For details, see Marking Specified Data in the Table.

You can create calculation fields with calculations. You can create calculation fields by using the following two main types of calculations.

Calculations in the self-service dataset: It refers to detailed information-level calculations (detailed row-level calculations) on the data source through the Formula Column function.

You can create visual components in the dashboard using this dataset in which the calculation results can also used by other users.

Note:

Aggregation calculations are not supported.

Calculations in the component: It refers to aggregation calculations or calculations in other self-service datasets through the Add Calculation Field function during component creation. The calculation results of the component are not saved in the dataset.

If you have decided the to-be-used calculation type, you can create a calculation field.

Creating a Calculation Field with Self-Service Datasets

You want to find out and mark cells where the value of the Province field is Shandong. If the field contains this value, the field returns 1, otherwise, displays 0.

1. In FineBI, click My Analysis to go to the analysis subject editing page, and click the Data tab at the bottom to edit data.

2. Click Formula Column, enter the name of the to-be-added formula column, and enter the formula IF(Find("Shandong",Province),1,0).

Note:

You can select functions and fields from the left area to add them to the formula box. You can also manually enter functions and fields in the formula box. (The system will automatically match them.)

The saved data can be published to Public Data for others to use during component creation. For details, see Data Publish.

Aggregation functions are supported in components, but not in self-service datasets.

Click the  button to add a component, click the  icon, select Add Calculation Field from the drop-down list, set Field Name to Cumulative Sales, and enter the formula ACC_SUM(SUM_AGG(Sales),1). Though this formula, sales are summarized cumulatively by dimensions, as shown in the following figure.

After added successfully, the calculation field can be used in the component.

You can drag the calculated fields into Dimension or Indicator on the component editing page, as shown in the following figure.