Quick BI:Create Calculated Field

Configuration method

1. On the data preview page, click Create Calculated Field.

   

2. On the Create Calculated Field page, set the parameters and then click OK.

   1. Enter a field name.

   2. Use Click To Reference Functions and Click To Reference Fields to edit the field expression. The referenced functions are categorized into built-in functions and database functions.

      Built-in functions: These are calculation functions integrated into the BI system to accommodate various databases by abstracting differences in underlying databases.

      For guidance on using built-in functions, see Built-in Functions.

      Database functions: These are native functions specific to the underlying database, and they vary between different databases.

      Keep the following in mind when editing an expression:

      • You can also type [ in the expression editor to prompt a list for inserting a measure field name.

      • Ensure that the parentheses and brackets in the Field Expression field are half-width characters, meaning they should be entered in English input mode.

      For common field expression examples, see Example Scenario.

   3. Choose Data Type and Field Type, and then click OK.

      

      When selecting Measure as the data type, you can specify the format expression as integer, one decimal place, two decimal places, percentage, percentage with one decimal, percentage with two decimals, custom, or manual input.

      The manual input format, such as #,##0.00%, can include uppercase and lowercase letters, numbers, and the symbols _#,.%, and must not exceed 50 characters. For details on using the format, refer to Excel's custom format syntax.

      

   The newly created calculated field can be directly used for further calculations to accommodate complex calculation scenarios.

   For instance, Profit Amount = Order Amount - Cost Amount; Profit Margin = Profit Amount / Order Amount.

Example scenario

• Aggregate function

  • Sum: SUM([Field])

  • Count: COUNT([Field])

  • Count with Remove Duplicates: COUNT(DISTINCT [Field])

  • Average: AVG([Field])

  Quick BI automatically aggregates dimension data on a dashboard for calculated fields configured for aggregation.

• Basic operations

  • Supports addition, subtraction, multiplication, and division

  • For example, to calculate the average transaction amount per customer from the order details table: SUM([Transaction Amount]) / COUNT(DISTINCT [Customer Name])

• Complex grouping scenarios

  For dimension grouping scenarios, Quick BI supports simple single-field grouping using the Group Dimension feature. For multi-field condition-based grouping, use the CASE WHEN function. For example,

  CASE 
  WHEN [order_amt]>5000 AND [order_number]>40 THEN 'VVIP' 
  WHEN [order_amt]>1000 AND [order_number]>5 THEN 'VIP' 
  ELSE 'Ordinary' END

  

• Character concatenation

  To merge fields such as province and city into one, use the CONCAT function.

  CONCAT(a, b, c, d): Place the fields to be merged inside parentheses, separated by commas. To include a delimiter between columns when merging strings, enclose the delimiter in single quotation marks (').

  For instance, CONCAT([Province],'-',[City])

• String processing

  String functions are available for text data processing.

  The method of string processing depends on the database functions. Here, MySQL functions are exemplified.

  • To truncate a string:

    SUBSTRING([Customer Name],1,1): Retrieves one character starting from the first character of the [Customer Name] field.

    SUBSTRING([Field]): Specify the start position and the length of the string to be truncated.

  • To find the position of a substring within a string:

    INSTR([Customer Name],'e'): Determines if the [Customer Name] field contains the character ‘e’ and returns the character's position if found; otherwise, it returns 0.

• Date and time processing

  Quick BI automatically splits date and time field values into multiple time granularities. You can set display formats for different time granularities to match business requirements for date and time formats in various scenarios.

  Database date-related functions can also be used for data processing. Type DATE in the search boxto find related functions in the function list.

  The method for date and time processing varies with the database functions. In this example, MySQL functions are used.

  • Current date: CURRENT_DATE

  • Days of employment: DATEDIFF(CURRENT_DATE, [Hire Date]), which returns the number of days from the hire date to today.

  • Expiration date: ADDDATE([Payment Date], 365), which calculates the date 365 days after the payment date.

• LOD function detailed level calculation

  To incorporate a dimension with a granularity different from the current visualization level in the view without altering the existing chart content, use the level of detail expression feature. For instance:

  • To calculate total sales for each region: lod_fixed{[Region]:SUM([Order Amount])}

    

  • To analyze customer repurchase behavior: lod_fixed{[Customer ID]:COUNT(DISTINCT([Order ID]))}

    

    For more details, see Analysis Functions (LOD Functions).