Quick BI:Create Calculated Field

configuration-methods

1. On the Data Preview page, click Create Calculated Field.

   

2. In the Create Calculated Field dialog box, set the parameters and click OK.

   1. Enter a field name.

   2. Click Reference Function and click Reference Field to edit the field expression. The referenced functions are classified into common functions and LOD functions. For more information about LOD functions, see LOD functions.

      When you edit an expression, take note of the following points:

      • After you enter a left bracket ([) in the Field Expression field, a measure list appears for you to select the desired measure.

      • The parentheses and brackets that you entered in the Field Expression field must be half-width characters. That is, the brackets entered in the English input method.

      • You can use only the functions that are supported by each data source.

        You can view the functions that are supported by a specific data source in the function section on the right side of the New Calculated Fields dialog box or query the functions that are supported by the data source type.

      For more information about common field expression examples, see Scenarios.

   3. Set the Data Type and Field Type parameters and click OK.

      

      If you set the Data Type parameter to Measure, you can set Value Format to Integer, Retain 1 Decimal Number, Retain 2 Decimal Number, Percentage, Percentage 1 Decimal Number, Percentage 2 Decimal Number, Custom, or Manual Input.

      The display format, such as #,##0.00%, can contain only letters, digits, and_#, %, and can be up to 50 characters in length. For more information, see Excel custom format syntax.

      

      You can also directly reference the newly created calculated field to perform secondary computing. This meets your requirements in complex computing scenarios.

      For example, profit amount=order amount-cost amount; profit rate=profit amount /order amount.

Sample scenarios

• Aggregate functions

  • Sum: SUM([field])

  • Count: COUNT([field])

  • Count deduplication: COUNT(DISTINCT [field])

  • AVG([field]):

  Quick BI automatically aggregates the data of the dimensions that are configured on a dashboard for the calculated fields that are configured for aggregation.

• Basic operations

  • Addition, subtraction, multiplication, and division are supported.

  • For example, you can use the following statement to calculate the unit price: SUM([transaction amount] )/ COUNT(DISTINCT [customer name])

    Note

    If the denominator is 0, process data in calculated fields to avoid calculation errors.

    CASE 
    WHEN [Customer name] is null THEN null 
    ELSE SUM([Transaction amount] )/COUNT(DISTINCT [Customer name]) 
    END

• Complex grouping

  You can use the Grouping Dimension function to group fields by dimension. If you want to group fields by condition, you can use the CASE WHEN function. Example:

  CASE 
  WHEN [Transaction amount]>5000 AND [Number of transactions]>40 THEN 'VVIP' 
  WHEN [Transaction amount]>1000 AND [Number of transactions]>5 THEN 'VIP' 
  ELSE 'NORM' END 

• String merging

  If a table contains the province and city fields, you can use the CONCAT function to merge provinces and cities into one field.

  CONCAT(a, b, c, d): Separate multiple fields with commas (,) in parentheses. If you want to add a delimiter between multiple columns when you merge strings, you can enclose the delimiter with a pair of single quotation marks (').

  For example, CONCAT([province],'-', [city])

• String processing

  You can use string functions to process text data.

  The string processing method varies based on database functions. In this example, MySQL functions are used.

  • Truncate a string.

    SUBSTRING([Customer name],1,1): the customer name field. The value must start from the first character.

    SUBSTRING([Field]: the length of the truncated string.

  • Find the position of a substring in a string.

    INSTR([customer name], 'east'): checks whether [customer name] contains the word 'east'. If yes, the location is returned. If no, 0 is returned.

• Date and time processing

  Quick BI automatically splits the values in the date and time fields into multiple time granularities. You can specify display formats for fields of different time granularities to meet the business requirements for different time formats of date and time granularities in different scenarios.

  You can also use database date-related functions to process data. You can enter DATE in the search box to search for related functions in the function list.

  The date and time processing method varies based on database functions. In this example, MySQL functions are used.

  • Current date: CURRENT_DATE

  • Entry Days: DATEDIFF(CURRENT_DATE, [Entry Date]).

  • Due Date: ADDDATE([Payment Date], 365) returns the date 365 days after the payment date

• LOD Function Detail Level Calculation

  If you need to add a dimension whose detail level is higher or lower than the detail level of the existing view, but you do not want to change the content of the existing graph, you can use the detail level expression feature. Examples:

  • Calculate the sum of sales in each region: lod_fixed{[region]:SUM([order amount])}

    

  • Analyze customer re-purchase status: lod_fixed{[user ID]:count(distinct([order ID]))}

    

    For more information, see LOD functions.