In the visual query module of Intelligent Data Insight, fields are categorized into two concepts: "dimension" and "metric". Text/time data is recognized as dimension fields by default, and numeric data is recognized as metric fields by default; in actual use, dimension and metric fields can be converted into each other. This article will introduce the specific features of metric field configuration.

2.1 Set aggregation method #

Function description: Metric fields display aggregated results in charts, and support modifying the aggregation method for metric fields (the field itself must not contain an aggregation function).
Default state: Metric fields are aggregated by "sum" by default.
Scope of use: Metric fields whose expressions do not contain aggregation functions.
Example:

• If the metric field is Order Amount, you can set the aggregation method.
• If the metric field is count(Order ID), which contains the aggregation function count, then the aggregation method for this field is "count" and cannot be changed to other aggregation methods.

Aggregation methods: The system supports the following aggregation methods. Among them, "count distinct (fast)" is supported from version V2.50.0 and later.

• Sum (default state)
• Count

Example: For the Name field, if the detailed results are "Xiao Zhang", "Xiao Li", "Xiao Zhang", the "count" aggregation result is 3.

• Count distinct (fast): Uses the uniq function for fast calculation, and the result is an approximate value. It performs efficiently when querying large amounts of data.
• Count distinct:

Example: For the Name field, if the detailed results are "Xiao Zhang", "Xiao Li", "Xiao Zhang", the "count distinct" aggregation result is 2.

• Average
• Maximum
• Minimum
• Variance
• Standard deviation
• Quantile: Supports 1%, 5%, 10%, 25%, 50% (median), 75%, 90%, 95%, and 99% quantiles.

*Meaning of quantile: Sort the data in ascending order. The data at the n% position is the n% quantile of this dataset. For example, among the five data points 1, 3, 5, 7, and 9, the 50% quantile is the third number, 5.
Operation steps:

• Click the field
• Select "Aggregation method"
• Select the required aggregation method, such as "Maximum"

• Modification completed

Caution: The current version does not support changing the aggregation method for metrics that already contain aggregation functions.

2.2 Set the data format #

Feature description: You can set the numeric format for numeric fields.
Scope of use: Numeric fields (these can be original numeric fields or fields where the final result of the expression is numeric)
Default state: The default format for integers is the original value; the default format for decimals is a number format that retains 4 significant digits, includes a thousands separator, and does not include a numeric unit.
Data format:

• Number (default option for decimals):
  • Numeric unit: Supports thousand, ten thousand, million, ten million, hundred million; K (Thousand), M (Million), B (Billion)
  • Decimal places: Default is 4
  • Significant digits for decimal places: When this option is selected, the number of significant digits after the decimal point equals the set number of decimal places. Not selected by default
    • Example: 12345.0012345
      • If decimal places is set to 3 and "Significant digits for decimal places" is not selected, the result is 12345.001
      • If decimal places is set to 3 and "Significant digits for decimal places" is selected, the result is 12345.00123
  • Thousands separator
    • Example: 123456789.123456789
      • If "Include thousands separator" is selected, the result is 123,456,789.123456789
      • If "Include thousands separator" is not selected, the result is 123456789.123456789
• Percentage:
  • Decimal places: Default is 2
  • Significant digits for decimal places: When this option is selected, the number of significant digits after the decimal point equals the set number of decimal places. Not selected by default
    • Example: 12345.0012345%
      • If decimal places is set to 3 and "Significant digits for decimal places" is not selected, the result is 12345.001%
      • If decimal places is set to 3 and "Significant digits for decimal places" is selected, the result is 12345.00123%
• Per mille: Configurable options are similar to percentage
• Original value (default option for integers): Uses the data format from the database
• Number, percentage, and per mille support data prefixes and suffixes

• The data format of a field supports customization

In the field settings of visual queries, users can configure different data formats for the same field based on conditions. In customization, set the necessary conditions. Users can define the rules for displaying data within different time ranges.

Operation steps :

• Click the field
• Select "Set data format"

• Switch the data format in the pop-up window and configure as needed. For example, change the number of decimal places to 2

• Configuration complete

2.3 Parse text #

Fields support parsing content in specific formats.

• Images and videos: When the field content is an image or video link, parsing as an image or video allows online preview of the image or video.

• Links: When parsing the field content as a link, clicking the cell opens a new tab and accesses the cell content as a link.

2.4 Set conditional formatting #

Conditional formatting allows you to mark cells with colors or icons, making important or abnormal data more prominent. The platform supports four types of conditional formatting for tables: single color, icon set, color scale, and data bar. Date fields also support conditional formatting configuration.
Supported range: tables and detail tables.
Operation steps:

• Click the field
• Click "Conditional formatting"

Caution: The configuration of icon set, color scale, and data bar conditional formatting for dimension fields is based on the metric fields dragged into the query.

• Single color:
  • The single color feature allows you to set the text and background color of cells that meet the conditions individually. It supports multiple condition settings and adding multiple rules to meet more business needs.

• For single color, the selectable fields are dimensions or metrics in the table. Time-type data cannot be used as a condition for single color configuration.
• The default scope of single color is the cell. Starting from version V2.42, the scope can be set to either the cell or the entire row.

warning

• When the same cell contains both cell-level and entire-row single-color conditional formatting rules that conflict, the cell-level rule takes precedence over the entire-row rule.
• When the same cell has multiple conflicting entire-row single-color conditional formatting rules, the rule corresponding to the field that appears earlier in the table takes precedence. As shown in the figure below, with the same settings, changing the field order will result in different display outcomes.

• Icon set: Icon sets provide a more intuitive way to categorize and display data. Data is divided into several categories based on thresholds, and data in the same category is displayed using the same icon set.

• Color scale: Color scales help users better understand the distribution of data. The larger the value, the closer the cell color is to the color corresponding to the maximum value; the smaller the value, the closer the cell color is to the color corresponding to the minimum value.

• For scenarios with outliers or where a customized color scale is needed, users can configure the maximum and minimum values themselves.

  

• Data bar: Data bars help users identify larger or smaller values. The longer the data bar, the larger the value; the shorter the data bar, the smaller the value. You can set the minimum value (the starting position of the data bar) and the maximum value (the end position of the data bar) to make the data bar better adapt to different data distributions.

• This applies to the subtotal function. When Total is used in the table and there is a subtotal row, you can configure whether conditional formatting applies to the subtotal row. By default, it applies to the subtotal row.

• Click the corresponding conditional formatting settings to select or deselect "Apply to subtotal". If you apply after deselecting, conditional formatting will not be displayed in the subtotal row.

• You can customize the display style and direction of the data bar.

2.5 Visual style #

To enrich the table's appearance, you can set the header text, header background color, body text, and alternating row text in the table body.

Supported scope: Table, detail table

2.6 Hide fields #

Feature description: You can hide columns or fields that are required for the chart but do not need to be displayed, to avoid showing redundant information.
Scope of use: Any field in a table or detail table can be hidden; indicator fields in pivot tables or other charts can also be hidden.
Operation steps:

• Click the field and select "Hide field".
  
• The field is hidden. If the chart type is a table, the field is hidden; if the chart type is a chart, the corresponding indicator is not displayed.