Quick BI:Create a calculated field

Configuration method

1. On the Preview tab of the dataset creation page, click Create Calculated Field.
   
2. In the New Calculated Fields dialog box, configure the parameters and click OK.
   1. Enter a field name in Label Name.
   2. Select fields in the Click to reference field and Click to reference function sections and edit the field expression.
      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.
      • 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 the examples of common field expressions, see Examples.
   3. Specify Data Type and Type. Then, click OK.
      

Examples

• Aggregate functions

  • Sum: SUM([field])
  • Count: COUNT([field])
  • Count with deduplication: COUNT(DISTINCT [field])
  • Average: 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 SUM([transaction amount])/COUNT(DISTINCT [customer name]) to calculate the unit price.
    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

  If you want to group dimensions, you can create group dimensions to group the dimensions based on a single field. If you want to group data based on the combination of multiple fields, 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 'normal' 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): Add the fields that you want to merge to parentheses and separate the fields with commas (,). 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 (').

  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): specifies to extract the first character of the [customer name] field.

    SUBSTRING([field]: specifies the substring that you want to truncate from the start of a string.

  • Find the position of a substring in a string.

    INSTR([Customer name], 'East'): checks whether [Customer name] contains the word 'East'. If the word exists, the position of the word in the customer name is returned. If the word does not exist, 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: returns the current date.
  • DATEDIFF(CURRENT_DATE, [Recruitment date]): returns the number of days from the day when an employee is hired in an enterprise to the current day.
  • ADDDATE([Payment date], 365): returns the date that is 365 days after the payment date.