Function calculation format

1.1 Version

1.2 Feature introduction

Users need to frequently use functions for calculations, so correct and effective function writing is very important.

This article introduces the composition and correct format of the FineBI function.

The FineBI function calculation consists of four contents:

For example, assume the following calculations: IF(Sales>"5000","high","low")

The corresponding calculation content is as follows:

Note: Not all calculations need to include all four components. For example, calculations may not contain text expressions, such as the calculation SUM_AGG(PRICE)/SUM_AGG(QUANTITY), which only includes the function SUM_AGG, the division operator (/), and the fields "PRICE" and "QUANTITY".

In addition, calculations can also include:

• The filter component participates in the calculation as a parameter-it can be inserted into the calculation to replace the placeholder variable of the constant value. For details on how to use and format each component in the calculation, see the sections below.

For details on common function syntax: Common function syntax FAQ

3.1 Overview

3.2 Function syntax

In FineBI, functions are the main component of calculations.

1）Functions are displayed in blue in FineBI calculations, and each function has a specific syntax.

For example: Create a dashboard in FineBI, add calculation indicator, and click the icon of the function location in the open calculation editor, and a function list will appear. Next is the "fields selection position". When you select a function from the function list, the rightmost part will be updated to contain the necessary syntax (1) and description (2) information about the function, with one or more examples (3), as shown in the figure below:

2）Multiple functions can be used in calculations. For example IF(SIGN(profit)=1,profit,0)

There are two functions in this calculation: IF and SIGN.

The function is included in another function (ie nested), which is the case in the example above. In this case, the "SIGN" function of "profit" will be calculated before the "IF" function is calculated, because the latter is inside the parentheses.

3.3 Field syntax

You can insert fields in calculations. The syntax of the function usually indicates where in the calculation the field should be inserted. For example: SUM_AGG(array).

For example: if you want to calculate the unit price, the calculation will use the "PRICE" and "QUANTITY" fields in the data source, enter the formula: SUM_AGG(PRICE)/SUM_AGG(QUANTITY), click on the field selection area on the left to select the desired field, As shown below:

Note: The field is displayed as a light blue background in the FineBI calculation.

3.4 Operator syntax

To create calculations, you need to understand the operators supported by FineBI. Operators are shown in black in FineBI calculations.

3.5 Text expression syntax

This section describes the correct syntax for using text expressions in FineBI calculations.

The text expression represents a constant value as original. When using functions, sometimes you need to use text expressions to represent numbers, strings, dates, etc. Text expressions are displayed in black and gray in FineBI calculations.

For details, please refer to section 3.1 of this article.