BI function corresponds to excel function- FineBI Document

Last update： 2022-05-10

1. Overview

Finebi supports the use of a variety of formula and functions. These formula or functions are different from commonly used Excel functions. This file provides comparison to help users with the foundation of Excel to quickly use the formula and functions in BI. The file is continuously updating.

2. Aggregate function

FineBI formula / function	Excel function	Function description
SUM_AGG	SUM()	Sum a set of data.
AVG_AGG	AVERAGE()	Average a set of data.
MAX_AGG	MAX()	Find the maximum value of a set of data.
MIN_AGG	MIN()	Find the minimum value of a set of data.
COUNT_AGG	COUNT()	The COUNT function calculates the number of cells containing numbers and the number of numbers in the parameter list.

Attentions

1）SUM_AGG is generally used for summation after Detail filtering and division. If you want to sum only one value, you can drag the field directly to the indicator to sum automatically.

2) Use COUNT_AGG to calculate the number of cell rows with sales profit, that is, calculate the number of cells with non empty amount, as shown in the following figure:

You can also directly use"Counter", as shown in the following figure:

3. Statistical function

FineBI formula/function	Excel function	Function description
IF(boolean,number1/string1,number2/string2)	IF(logical_test, value_if_true, [value_if_false])	Conditional judgment
SUM_AGG and IF are used together. See file Conditional summation and counting	SUMIF/SUMIFS	Condition aggregation
COUNT_AGG and IF are used together. See file Conditional summation and counting	COUNTIF/COUNTIFS	Condition count
AVG_AGG combined with IF	AVERAGEIF/AVERAGEIFS	Conditional averaging

Attetions:

The detail filtering in BI is the filtering of the number of data details before calculation. It can be written in combination with the aggregation function, and the effect is the same as SUM_AGG combining with IF.

4. Logic function

FineBI formula/function	Excel function	Function description
Nested use of IF functions	IFS	Checks whether one or more conditions are met and returns a value that meets the first "TRUE" condition
Does not support	NOT	Logical inversion of its parameters
Nesting with IF and other functions	IFERROR	IFERROR returns the value specified when the formula calculation result is error. Otherwise, it returns the result of the formula.
AND	AND	Logic "AND"
OR	OR	Logic "OR"

IFERROR function is an example of the application in BI. For example, if the sales volume and sales amount are known, the sales unit price of the corresponding product

needs to be calculated.

Sample data: Sales status.xlsx

Upload data to FineBI, as shown in the figure below:

After creating a dashboard using a dataset, add a calculation field and enter a formula: IF(SUM_AGG(sales amount)=0,"Calculation error",SUM_AGG(sales volume)/SUM_AGG(sales amount)).

Since the sales unit price needs to be calculated, the aggregate function needs to be used: SUM_AGG(sales volume)/SUM_AGG(sales amount). At the same time, in order to ensure the correctness of the calculation results, it is necessary to use the IF function to judge whether the sales volume is 0. Since the IF function cannot use aggregate and non aggregate functions at the same time, the judgment SUM_AGG(sales amount)=0should also be used, as shown in the figure below:

Drag "product" and new fields into the analysis column, and the results are as follows:

Formula	Description	Result
IF(SUM_AGG(sales amount)=0,"Calculation Error",SUM_AGG(sales volume)/SUM_AGG(sales amount))	If the sales volume = 0, the division cannot take effect, and "Calculation Error" is the output.	Calculation Error
If the sales volume is not equal to 0, the calculation result is output.	0 333.333333333

Formula

Description

Result

IF(SUM_AGG(sales amount)=0,"Calculation Error",SUM_AGG(sales volume)/SUM_AGG(sales amount))

If the sales volume = 0, the division cannot take effect, and "Calculation Error" is the output.

Calculation Error

If the sales volume is not equal to 0, the calculation result is output.

333.333333333

5. Reference function

FineBI formula/function	Excel function	Function description
First, sort with self-service dataset, and write judgment and assignment with if function for self-service dataset/dashboard.	match	Find the number of location rows equal to / close to the query value

Example of MATCH function application in BI:

For example, if you need to calculate the sales revenue ranked first in ascending order, you can first rank it using the self-service dataset, and then use the function to return the specified data, as shown in the following figure:

The results are shown in the figure below:

6. Text function

FineBI formula/function	Excel function	Function description
LEN	LEN	Find field length
LEFT Does not support LEFTB	LEFT、LEFTB	Left intercept
RIGHT Does not support RIGHTB	RIGHT、RIGHTB	Right intercept
MID Does not support midb	mid、midb	MID returns a specific number of characters in a text string starting at a specified position
TRIM	Trim	Clear all spaces at the beginning and end of the text
FIND Does not support Findb	Find、Findb	Find character position
Does not support Use FIND to look up the character position	search，searchb	Find character position
1）CONCATENATE 2）Self service dataset > group summary > string splicing Note: the CONCATENATE function returns the text type field by default when splicing.	concatenate	Combine several strings into one string
1) Add a calculation indicator and use the IF function to convert the value to the specified text 2) The default returned after CONCATENATE splicing is the text type, and text conversion is not required.	text	The TEXT function applies a format to a number through the format code, thereby changing the display of the number.

7. Numerical function

FineBI formula/function	Excel function	Function description
ROUND	round	The ROUND function rounds a number to the specified number of digits.
MOD	mod	Returns the remainder of the division of two numbers. The sign of the result is the same as the divisor.
Not supported, can be converted by adding calculation fields.	conver	Measure conversion.
Add a calculation indicator and multiply the two fields directly.	sumproduct	Returns the sum of the number of corresponding ranges or arrays. The default operation is multiplication.
RANK_ANLS	rank	Returns the numerical ranking of a column of numbers.
Summary mode	SUBTOTAL	Returns a subtotal in a list or database.

8. Date function

FineBI formula/function	Excel function	Function description
WEEKDAY Note: the default return value is an integer between 0 and 6, representing a day of the week (from Sunday to Saturday).	WEEKDAY	Returns the day of the week corresponding to a date.
DATEDIF(start_date,end_date,unit)	DATEDIF	Returns the number of days, months, or years between two specified dates.
DATE	DATE	Returns the number of series for a specific date.
TODAY Note: from version 5.1.12, the date field numeric writing method is supported. For example, today()-1 is equivalent to returning the date of yesterday.	TODAY	Get current date.
YEAR	year	Returns the number of days in a year.

9. Operator

Meaning	FineBI function	Excel function
No equal to	!=	<>

Helpful
Not helpful
Only read

中文（简体）中文（繁體）日本語

English

BI function corresponds to excel function