Quick BI:Cross Table

Prerequisites

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

Overview

• Scenarios

  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.

  • Event capability: Data feedback reporting events.

  • 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 Area, Product Type, and Product Box. You can also drag these dimensions to the Rows area.

     • 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.

     

• 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.

     

• 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.

     

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

  

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.

  

• 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.

    

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

    

• 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.

    

    • 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.

      

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

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

      

    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.

    

• 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.

  

  • 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.

    

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

  

  • 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:

    

  • 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.

    

  • 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.

    

  • 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.

    

  • 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 icon to expand or collapse all categories.

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

  

  Chart configuration items	Description
  Custom Table Theme	The theme of the table. You can select Zebra Crossing, Wireframe, Simplified Edition, or Simplified Edition. You can select Theme Color, Gray, or Custom.
  Text	Set the cross table text style.
  Column Width	Set the Column Width parameter. You can select Adaptive by Container or Custom.
  Enable	If you select this check box, the table content supports automatic line wrapping.

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

  

  Chart configuration items	Description
  Display mode	The theme of the table. You can select Zebra, Wireframe, Simplified, or Simplified. You can select Theme, Gray, or Custom from the Main Color drop-down list.
  Freeze	Specifies whether to freeze the table header or column.
  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.
  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.
  Sorting method	You can set the Sort Mode of a cross table. 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 to the right of the column header to select the ascending or descending order.
  Merge Row Dimension	You can specify Merge Row Dimensions. If you select this check box, the cross table combines the dimension information into one column and displays it by row. PC You can specify the row dimension name, row dimension column width, and whether to display row dimension fields in data. Mobile platform You can specify the row dimension name, row dimension column width, and whether to display row dimension fields in data. 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.
  Serial Number Column	After you select the check box, the report column header displays the serial number. ① You can set the name of the serial number column. ② You can select Do Not Display Serial Number in Total Rows. Note You can select Do Not Display Sequence Number in Total Rows only if you select Total Column Timing for the cross table.
  Pager	Click Pager icon to activate the Pager Edit page. By default, the Pager Mode parameter is set to regular Mode. If the data volume is too large, we recommend that you select Simple Mode. In this mode, the total number of data records is not calculated. You can select First Row Dimension or All Dimensions. Note When you perform global sorting, the first-row pagination automatically becomes invalid. You can select 10, 20, 30, 50, 80, 100, 200, or 500 entries per page.

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

  

  Parameter	configuration items Content	Description
  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	If a measure field exists in the row dimension, you can configure a custom measure header name.
  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.

  

  Chart configuration items	Description
  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.

  

  Chart configuration items	Description
  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, set Column Summary and Row Summary in the Total /Subtotal section. Column Summary and Row Summary.

  Column totals support pinned at the bottom or pinned at the top.

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

• In Filtering

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

    

    If the Form Filling event is configured and the fields that are bound to the form are canceled, the form filling cannot be used. For more information, see Field mapping.

    • Supports setting the default selected dimension.

    • You can set the Maximum Dimension parameter.

      For example, if you set Maximum Available Dimensions to 6, you can select a maximum of six dimensions for display.

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

    

    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.

    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.

    

    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.

    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.

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

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

  • If you set Display Mode to Select, the scroll bar is displayed only when you move 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.

    

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.

• Change the number of data records that are displayed on each page.

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

Chart analysis configuration

Export data

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

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.

  • 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).

    • 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).

• 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.

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. 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%.