Getting started with Calculation

This article describes how to create and use calculation functions in FineBI using examples.

It explains FineBI calculation concepts and how to create and edit calculated fields; how to use the calculation editor and how to use calculated fields in the component editing interface.

This is a good place to start if you are not familiar with FineBI calculations or with creating calculated fields in FineBI.

Calculated fields allow for the creation of new data using data that already exists in the data source.

When you create a calculated field, you are essentially creating a new field (or column) in the data source whose value or membership is determined by the calculation under control. This new calculated field will be saved in FineBI and can be used to create charts. But don't worry: the original data will remain the same.

Calculated fields can be used for many reasons. Some examples might include:

• Split the data

• Convert the data type of a field, for example converting a string to a date.

• Aggregate the data(For dashboards only Add calculated fields)

• Screen results

• Calculate the percentage

The scenarios using the calculation are given below.

2.1 Lack of data for analysis in data source

The built-in DEMO "Sales statistics of stores" data table has "Sales" and "Gross profit" fields, but if you need to calculate "Cost", you can use a formula similar to the following to calculate the "Cost" field, as shown in the following figure.

2.2 Convert values in the component

For example, the cost percentage of different stores that may be calculated can be displayed in the component using a quick calculation of the cost percentage of different stores, as shown below.

2.3 Quickly categorize data

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

For more information: Specifying Data in a Markup Form

Calculations can be used to create calculated fields. The following two main types of calculations create calculated fields in FineBI.

• Self-service dataset calculations: Add a new column to the self-service dataset to perform data source detail level (row detail level calculations), use this dataset to create visualization components in the dashboard, and the results of this "dataset" calculation can be used by other users. Note: Aggregate calculations are not supported.

• Dashboard Calculation: When creating a component in the dashboard, Add calculation fields to "Aggregate Calculation" or "Calculation in other self-service datasets",  the results of the component calculation will not be saved in the dataset.

Once you have determined the type of calculation you want to use, you can create the calculated fields.

4.1 Create fields using self-service datasets

1)In FineBI, select "Data Preparation" to enter the business package, and click "Add Table > Self-Service Dataset" as shown below.

Select all fields under "Regional Data Analysis" and add new columns as shown below:

Select "Formula/Function" and enter the name of the new column calculated field. In this example, the field is called "Test", enter the formula, this example uses the following formula: IF(Find("Province", Province),1,0), if the field contains "Province", it returns a 1, otherwise, it is displayed as "0", when finished, click "OK", as shown below.

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

Saved self-service datasets are available for others to use and can be used when creating components in the dashboard.

4.2 Create Calculated Fields in dashboard

Dashboard supports more aggregation functions than self-service datasets.

Create the component and select "Sales DEMO>Regional Data Analysis", as shown in the following figure.

Create a new field "Amount of cumulative return" and enter the formula: ACC_SUM(SUM_AGG(Amount of return),1), which indicates the cumulative summation of the return amount by dimension, as shown in the following figure.

Once completed, it is ready to be used in the component.

Drag the calculated fields into the "Dimension" or "Indicator" field in the component editing interface, as shown in the following figure.

If you need to change the calculation at any time, you can edit the calculation fields.

6.1 Edit new fields in the Self-service dataset

Select the created self-service data set, click "Edit", select the corresponding new field location, and click Set Function/Formula to make changes, as shown in the following figure.

6.2 Edit calculated fields in the dashboard

Select the new calculated field and click "Modify Calculated Field", as shown in the following figure.