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() | |
| AVG_AGG | AVERAGE() | |
| MAX_AGG | ||
| MIN_AGG | ||
| COUNT_AGG | The COUNT function calculates the number of cells containing numbers and the number of numbers in the parameter list. |
Attentions
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 |
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 |
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 | |
|---|---|---|
| LEN | LEN | Find field length |
LEFT | 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 | Clear all spaces at the beginning and end of the text | |
Does not support Findb | Findb | Find character position |
Does not support Use FIND to look up the character position | ||
1）CONCATENATE 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
8. Date 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 | Returns the number of days in a year. |
9. Operator
| Meaning | FineBI function | Excel function |
|---|---|---|
| No equal to | != | <> |
