Function Calculation Format

  • Last update:May 15, 2023
  • 1. Overview

    1.1 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.

    2. Function composition

    The FineBI function calculation consists of four contents:

    ContentsDescription

    Function

    Statements used to convert values or members in a field

    Field

    Dimensions or metrics in the user data table
    Operator

    Specify the symbol of the operation

    Text expression

    Constant value according to the written content

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

    51.png

    The corresponding calculation content is as follows:

    Contents
    Description
    FunctionIF
    Field

    Sales

    Note: The field must be selected in the left field box, and cannot be entered manually.

    OperatorFor details on: Calculation operator and priority
    Text expression

    String: "high", "low"

    Number text: 5000

    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.

    3. Calculation grammar

    For details on common function syntax: Common function syntax FAQ

    3.1 Overview

    Component
    Utilized locationGrammarExample
    FunctionAdd columnsfilteradd calculation indicators

    For details of function syntax, check the corresponding function: Math and trigonometric functiontext functiondate functionlogic functionother function 

    Aggregate functionfast calculation function are only available when adding calculation indicators

    SUM_AGG(PRICE)
    Field
    Fields need to be selected in the field selection area on the leftPRICE(light blue background)
    Operator
    SUM_AGG(PRICE)/SUM_AGG(QUANTITY)
    Text expression

    Number text is written as string.

    String text and date text are enclosed in quotation marks.

    Boolean text is written as true or false.

    Null text is written as null.

    "5000"

    "high"、“2020-07-15”

    true or false

    null

    The filter component participates in the calculation as a parameterAdd calculation indicators

    The filter component value becomes a parameter value, and the name of the filter component is the parameter name

    The writing method is the same as the "field"

    IF(Amount>Value drop-down filter component, "large order", "small order")

    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.png

    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:

    格式3.png

    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.

    附件列表


    主题: Advanced Data Analysis
    Previous
    Next
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy