All Products
Search
Document Center

Quick BI:Cross Table

Last Updated:Jan 25, 2024

A cross table shows the sum of field values in a table and merges similar data. Data in a cross table is grouped into columns and rows. The rows are on the left side, and the columns are on the upper side. You can use aggregate operations such as SUM, AVG, COUNT, MAX, and MIN to calculate data in the cell at which a column and a row intersect. This topic describes how to add data to a cross table and configure the style.

Prerequisites

A dashboard is created. For more information, see Create a dashboard.

Overview

  • Scenario

    Cross tables are used to analyze the relationships between multiple dimensions and measures to help decision-making.

  • Benefits

    • Computing capabilities: You can configure advanced computing capabilities such as month-on-month comparison, cumulative calculation, percentage, ranking, top N, percentile, and total subtotals.

    • Data visualization: You can configure the table theme, tree display mode, cell freezing, auto line wrap, column width, and other display styles for cross tables.

    • Interactive options: You can add custom remarks or comments to cross tables. You can also add links to cross tables to interact with external systems.

    • Conditional formatting: You can use various formats, such as font colors, background colors, icons, color scales, and data strips, to improve user experience.

    • Data filtering: You can filter dimensions or measures to create cross tables, or filter data that is displayed in cross tables.

  • Example效果示意图

Create a cross table

A cross table consists of rows and columns. You can specify dimensions or measures for rows and columns.

  • Specify dimensions for Rows and measures for Columns.

    1. On the Data tab, select measures and dimensions based on your requirements.

      • In the Dimensions list, double-click province, product_type, and product_box. You can also drag the dimensions to the Rows section.

      • In the Measures list, double-click Order Amt, Shipping Cost, and Profit. You can also drag these measures to the Columns field.

    2. Click Update to create the LBS heat map.

      image.png

  • Specify dimensions for Rows, and specify measures and dimensions for Columns.

    1. On the Data tab, select measures and dimensions based on your requirements.

      • In the Dimensions list, double-click product_type and product_box. You can also drag the dimensions to the Rows section.

      • In the Dimensions list, drag province to the Columns section.

      • In the Measures list, double-click Order Amt, Shipping Cost, and Profit. You can also drag these measures to the Columns field.

    2. Click Update to create the LBS heat map.

      image.png

  • Specify measures for Rows and dimensions for Columns.

    1. On the Data tab, select measures and dimensions based on your requirements.

      • In the Dimensions list, drag area, province, and product_type to the Columns field.

      • In the Measures list, find Order Amount, Shipping Cost, and Profit Amount and drag them to the Lines field.

    2. Click Update to create the LBS heat map.

      image.png

Note

The numbers of dimensions and measures of a cross table are not limited.

You can export up to 10,000 rows of data from a cross table. The period of time that is required to export the data varies based on the configurations of your on-premises machine and the size of the data you want to export. For more information, see Export data.

  • In the Filter section, set filter conditions. For more information, see Filter by field.

    image.png

Configure parameters on the Data tab

On the Data tab, you can perform the following operations:

  • Add multiple fields.

    Click Shift/command to add fields to the target area.

    交叉表-插入字段.gif

  • Hide fields.

    Dimensions and measures that are specified in the Rows section or Columns section can be hidden.

    • If you hide a dimension or measure in the Rows field, this dimension or measure is not displayed in the table. The values of the dimension or measure can be used for calculations and grouping.

    • If you hide a dimension or measure in the Columns field, this dimension or measure is not displayed in the table. The values of the dimension or measure can be used to calculate grand totals.

  • The alignment mode.

    • Horizontal alignment supports setting left alignment, center alignment, right alignment and automatic.

      image.png

    • Vertical alignment supports setting top alignment, center alignment, bottom alignment and automatic.

      image.png

  • Sort data.

    • You can sort global data or group data in ascending order, descending order, or custom order. You can also sort data by field or by using advanced settings. For more information, see Sort data. You can determine whether to sort data based on your business requirements.

    • Sort By Field: You can sort dimensions by measure or by another dimension.

      image

      • You can sort data based on a measure that you added on the Data tab.

        (1) Select a measure that is added to the Field tab.

        (2) Select one of the following sorting orders: ascending, descending, ascending in the group, and descending in the group.

        image

      • You can sort data by measure or dimension in your dataset.

        As shown in the following figure, you can sort by profit amount.

        交叉表-字段排序.gif

      Note

      Sort By Field is available only for dimensions.

    • Advanced Sorting: You can use the Advanced Sorting feature in aggregate tables and item tables.

      Advanced Sorting is available for dimensions in the Rows section and Columns section.

      image

  • Aggregate calculation: supports sum, average, count, deduplication, maximum, minimum, population standard deviation, sample standard deviation, population variance, sample variance, start time point, and last time point.

    image.png

    • Start Time: You can set the Aggregate Calculation Method parameter to Start Time and select a date field. After you set this parameter, the field is aggregated and displayed as the data at the start time in the cross table.

      image.png

  • Advanced computing, which supports date cumulative, cumulative, percentage, ranking, top N, percentile, and same-period comparison.

    image.png

    • For more information about date rollup, see Date rollup.

    • The cumulative calculation is used to accumulate the results returned from the data. For more information, see Cumulative calculation.

    • Percentage: You can set Percentage to view the percentage of each data.

      In this example, view the proportion of the order amount in each province. The following figure shows the effect:

      交叉表-占比.gif

    • Ranking: The grouped metrics are ranked based on their numerical values. This way, you can compare data in an efficient manner.

      For more information, see Data ranking.

      image.png

    • TopN: The top N data records are retained. For example, if you select Top5, the top 5 data records are retained. You can also customize the ranking method and the number of N data records. For more information, see TopN.

      交叉表-topn.gif

    • Percentile calculation is used to rank the results returned from data in the form of percentiles. You can view the position of a piece of data in a group of data. For more information, see Percentiles.

      image.png

    • Same-month comparison: You can configure the same-month comparison for the measure. For more information, see Same-month comparison.

Configure the styles of the cross table

The following section describes how to configure the styles of an LBS heat map. For information about the common style settings of charts, see Configure the chart title.

You can enter a keyword in the search box at the top of the configuration section to search for configuration items. You can click the image.pngicon to expand or collapse all categories.

115.gif

  • In the Basic Table Style section, configure the style of the cross table.

    image.png

    Chart configuration items

    Descriptions of parameters

    Custom Table Theme

    The theme of the table. You can select Zebra, Wireframe, Simplified, or Minimal. You can select Theme Color, Gray, or Custom.

    12

    Text

    Set the cross table text style.

    Column Width

    Set the Column Width parameter to Adaptive by Container or Custom.

    • Adaptive by Container: The system displays the column width based on the field value.

    • Custom: specifies the display width of the field value. The unit for width change is in pixels. The minimum value is 25.

    Note

    If you set Display Sequence Number and Display Method to Tree Display, you can customize the width of the sequence number column and tree column.

    image.png

    Enable

    If you select this check box, the table content supports automatic line wrapping.

    Note

    Automatic line wrapping is supported only when the column width is set to custom and fixed pixels.

    If the column width is set to Adaptive by Container or Custom is automatically selected, the configuration does not take effect.

  • In the Cell section, configure the display and sorting methods.

    image.png

    Chart configuration items

    Descriptions of parameters

    Display mode

    Display Mode: You can select Tiled Display or Tree-shaped Display. 13

    If you select Tree Display, you can customize the Row Label Name and Default Display Level. You can also use the Display Level Control button in the upper-left corner to set the display level.

    image.png

    Note

    If you select Tree-shaped Display, the dimensions in the Rows section cannot be hidden.

    Freeze

    Specifies whether to freeze the table header or column.

    Freeze: If you want to freeze all dimension columns, select Auto (Table Head).

    Metric Display Group

    You can configure Metric Group Display. After you enable Mixed Dimension /Measure, you can also group the fields of the dimension. At the same time, the group supports setting the field description.

    Merge Same Cells

    You can choose to merge or not merge similar cells.

    合并同类单元格..gif

    Display the metric name in a single metric column

    You can specify whether to display a single measure name when the dimensions and measures in a column are mixed.

    Sort Mode

    You can set the Sort Mode of a cross table.

    image

    • All Sorting Methods: displays all sorting methods in the All Sorting Methods drop-down list. You can manually change the sorting methods.

      排序-全部

    • Global Sort Only: If you want to click a column header to perform global sorting on a field, you can select Global Sort Only. After you select the check box, click the icon imageto the right of the column header to select the ascending or descending order.

      排序-全局

    Vertical Screen Style on Mobile Terminal

    You can set the Style parameter to Table or Consolidated Row Dimension.

    • If you select Workbook, the cross table is displayed on a mobile terminal in the same manner in which the table is displayed on the PC.

    • If you select Merge Row Dimension, multiple dimensions in the Columns section are merged into one column in the cross table that is displayed on a mobile terminal.

    When you set Mobile Terminal Portrait Mode to Merge Row Dimension, data of all dimensions are merged into one column, and the cross table displays the data by row. You can specify a custom column name for the merged dimensions and change the column width. 交叉表

    Note

    When the width of the cross table is more than half of the width of a mobile terminal screen, the cross table will be displayed across the screen.

    Show Sparkline

    Show Sparklines: You can enable this feature when measures are added to the Rows section.

    This option is available only when measures are added to the Rows section and columns are time fields.

    image.png

    Serial Number Column

    After you select the check box, the report column header displays the serial number.

    After selection, the report column header will display the serial number, and the serial number column name can be set.

    image

    Pager

    Specifies whether to enable the page splitter. You can select First Line Dimension or All Dimensions as the page type. After you enable this feature, you can view 10, 20, 30, 50, 80, and 100 entries per page.

    image..png

  • In the Header section, configure the style of the row header in the crosstab.

    image..png

    Parameter

    configuration items Content

    Descriptions of parameters

    List Header

    Do not display the list header

    If you select this check box, the contents of the list header will not be displayed.

    Background Color

    Sets the fill color of the list header background.

    text

    Sets the list header text style.

    Set the alignment mode

    Specifies the alignment of the head of the list. The left alignment, center alignment, and right alignment are supported.

    Custom Measure Header Name

    You can customize the name of a measure header.

    Row Header

    Background Color

    Sets the row header background fill color.

    text

    Sets the row header text style.

  • In the Conditional Formatting section, configure conditional formats for data. The following table describes the parameters.

    image..png

    Chart configuration items

    Descriptions of parameters

    Series

    You can select the fields that you added on the Data tab.

    Shortcut Style

    You can select the marker icons or font display colors.

    Custom Style

    You can configure the Text/Background, Icon, Color Scales, and Strip.

    Conditional styles take effect on summary data

    If you select this check box, the specified condition style also takes effect on the summary data.

    Synchronize Style To

    You can synchronize the conditional format that you specified to other fields.

  • In the Total /Subtotal section, configure Column Summary and Row Summary.

    总计..gif

    For the FAQ about calculating the totals, see FAQ about totals.

  • In Filter

    • After you select Field Content Filtering, you can click the Selected Fields drop-down list in the preview to filter or sort fields.

      拖动交叉表1

    • After you select Enable Shortcut Filter, you can click the Filter drop-down list in the preview state to filter fields.

      更新图表-合4.2需求

      If you want to filter multiple fields, you can add multiple conditions in the dialog box that appears after you click Filter.

      Example: The following figure shows how to filter cities for which the shipping_cost value is greater than 300 and the back_point value is greater than 0.5 in the South China region. 4.2

      Note

      If you close the editing page and then reopen the page, the filter conditions that you specified in the preview mode cannot be saved.

  • In the Auxiliary Display section, you can set the Display Mode and ScrollBar Threshold parameters.

    • If you set Display Mode to Slide into Display, the scroll bar is displayed only when you place the pointer over the table.

    • If you want the scroll bar to remain on, you can select Resident Display.

      滚动条显示

    • If you want to make the scroll bar more visible, you can adjust the thickness of the scroll bar.

      image

Modify the styles in a cross table

You can also perform the following operations to change the styles of a cross table:

  • Click the column that you want to use to sort the data. The following configuration options are displayed in the drop-down list. Configure the sorting order based on your business requirements. 界面配置_4.3.2

  • Change the number of data records that are displayed on each page. 修改条数_4.3.2

  • Click and drag the boundaries of a column to change the column width.

Chart analysis configuration

Field

Requirement

Filter Interaction

After you configure Linkage and click an area or field in the cross table, data in other charts or tables that are associated with this table is updated. For more information, see Configure the filter interaction feature.

Hyperlink

After you configure Hyperlink, you are redirected to the linked report when you click a field in the cross table. You can set Hyperlink to Parameter or External Link. If you select Parameter, you must configure global parameters. For more information, see the "Configure the hyperlink feature" section in Drilling, filter interaction, and hyperlink.

Display Secondary Indicator

If you select Display Secondary Indicator, you can configure the display modes of secondary indicators. For more information, see Display secondary indicators.

Export data

The export control feature is supported only when you turn on Export. For more information, see Export control.

image.png

Parameter

Parameter description

Export Name

You can specify a custom name for the exported file.

File format

You can export a file in one of the following formats: EXCEL, Image, and PDF.

Note

If you want to export an EXCEL file, you can set Cross Table Data Type to Export with Format or Pure Data.

  • If you want to export more than 50,000 cells of data, we recommend that you use Pure Data.

  • If you want to export data as an EXCEL file, only the first 10,000 records can be exported.

Organization administrators can configure the export format of data. For more information, see Configure the export feature.

Watermarking

This parameter is required only when you set File Format to EXCEL. You can specify whether to display watermarks based on your business requirements.

Export Channel

You can select Local to export data to your on-premises machine. You can also export data to the Object Storage Service (OSS) server.

Display secondary indicators

After you configure measures, you can configure secondary indicators.

After you select Display Secondary Indicator, you can select measures from the Select Secondary Indicator drop-down list.

  • Comparison Date: Select a time field from your dataset.

  • Select Field to Compare: The comparison fields are displayed based on the granularity of the time field. Up to four comparison fields can be displayed. For example, if you select a day field for Comparison Date, you can set Select Field to Compare to Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), or Compare (of Last Year). If you select a month field for Comparison Date, you can set Select Field to Compare to Compare (Month to Month) or Compare (of Last Year).

  • Show: You can select Percent Variance, Variance, or Original Value. The default value is Percent Variance.

    • Variance: The data display format is the same as the format of the metric.

    • Percent Variance: A percentage is displayed by using two decimal places.

  • Calculate the Difference Between Percentages: By default, this parameter is not selected. Unit: pt. If you select Variance and choose Percent Variance as the comparison indicator after you select Calculate the Difference Between Percentages, pt is automatically displayed. Example: 5% - 4% = 1 pt.

  • Display Location: You can select Right Side of Indicator, Right, or Below Indicator. The default value is Right Side of Indicator.

    • Right Side of Indicator: You can add a new column on the right side of the metric that you selected for comparison. The column header is named based on the selected comparison method. The name can be Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), or Compare (of Last Year).

      If the metric that you selected for comparison is a column header, the secondary indicator is displayed on the right side of the selected metric. 1

    • Right: The secondary indicator is separately displayed on the rightmost side of all metrics.

      • If the metric that you selected for comparison is a column header, the last column dimension is split to display the results of Compare (Day to Day) and Compare (of Last Week). In the following figure, All is used as an example to show a split column dimension. In this example, All is split into All (Day to Day) and All (of Last Week).14

      • If the metric that you selected for comparison is a row header, the last row dimension is split to display the results of Compare (Day to Day) and Compare (of Last Week). This usage scenario is similar to the usage scenario in which the selected metric is a column header. The difference between the scenarios is that the secondary indicator is displayed below the selected metric, instead of on the right side of the metric.

    • Below Indicator: You can add multiple columns of secondary indicators below the metric that you selected for comparison. The alignment of the added indicators is the same as the alignment of the select metric. The text is aligned to the left and the values are aligned to the right.

      The added indicators share the same cell as the selected metric. You can add a row below the row of the selected metric. The row is named based on the comparison method. The name can be Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), or Compare (of Last Year). 1

  • Display Style: The display style is determined based on the interface language of your on-premises machine. If you use Chinese, select Positive num show red and negative num show green. If you use English, select Positive num show green and negative num show red. After you configure the display style, the configured color remains unchanged regardless of the language.

  • Synchronous Comparison Indicator: You can synchronize the configurations of the secondary indicators to multiple metrics.

Limits

When you create a cross table, you must configure Rows and Columns.

  • You must specify dimensions for Rows, such as province and product_type.

  • You must specify measures for Columns, such as order_number and profit_amt.

Note

The numbers of dimensions and measures of a cross table are not limited.

You can export up to 10,000 rows of data from a cross table. The period of time that is required to export the data varies based on the configurations of your on-premises machine and the size of the data you want to export. For more information, see Export data.

FAQ about totals

Usage notes:

  • If your original data is GMV (SUM), the default calculation method is automatic. In this case, the logic that is used to calculate totals and subtotals is GMV (SUM). If you change the calculation method from automatic to custom and select AVG as the aggregate method, the logic that is used to calculate totals is GMV (AVG).

  • If your original data is id (COUNT (DISTINCT)), the default calculation method is automatic. In this case, the logic that is used to calculate totals and subtotals is id (COUNT (DISTINCT)). If you change the calculation method from automatic to custom and select COUNT as the aggregate method, the logic that is used to calculate totals is id (COUNT (DISTINCT)).

What is the logic that is used to calculate the rate?

  1. Define the rate.

    In this example, the rate is calculated based on the following formula: Rate = Metric A/Metric B.

  2. Add a calculation field to your dataset and define the expression as rate = sum(A)/sum(B). 3

  3. On the Data tab, select rate (SUM). In the Totals section, select Column Totals and Column Subtotals, set Calculation Method to automatic, and then use rate (SUM) as the default calculation logic. The logic that is used to calculate totals and subtotals is sum(A)/sum(B).

    Note

    For example, if your expression is rate=A/B, and you select Column Totals and Column Subtotals, set Calculation Method to automatic, and use A/B (SUM) as the calculation logic. In this case, the calculation result cannot be trusted because the value of the calculated result may be more than 100%.