This topic provides an overview of the latest cross tables and an example on how to configure the latest cross tables. This topic also describes how to adjust the display effects of the latest cross tables.

Prerequisites

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

Precautions

The following table describes the advantages of the latest cross tables over earlier cross tables and pivot tables.
Item Earlier cross table/Pivot table Latest cross table Advantage
Data retrieval logic Logical operations such as calculating grand totals and subtotals, pagination, and sorting are performed on a sample of 10,000 rows of data. Logical operations are performed on full data, and grand totals and subtotals are calculated based on the original configurations of indicators. Then, pagination is performed. Grand totals, subtotals, and sorting may affect the calculation results. Latest cross tables are an optimized version of earlier cross tables. Latest cross tables provide more accurate data because the results are calculated based on the full data.
Pagination Data pagination is performed at the frontend. This causes the Merge Same Cells feature to become unavailable. Data pagination is performed at the backend. Data retrieval and query are triggered each time you click Next. The Merge Same Cells feature is available in this case. The changes to the pagination mechanism improve the accuracy of the calculated results and the Merge Same Cells feature.

Overview

A cross table shows the sum of field values 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.

When you create a cross table of the latest version, you must specify Rows and Columns.
  • You must specify dimensions for Rows.
  • You must specify measures for Columns.
Note The number of dimensions and measures for a cross table are not limited.

You can export up to 10,000 rows of data from a cross table. However, the time required to export the data varies based on the configurations of your computer and the size of the data you want to export. For more information, see Export data.

Configure the cross table

  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 these dimensions to the Rows field.
    • In the Measures list, double-click order_number, shipping_cost, and profit_amt. You can also drag these measures to the Columns field.
  2. Click Update to update the table. Update - 3.12.1

If fields of the image type are included in your dataset, the latest cross table displays the image fields.

Configure parameters on the Data tab

On the Data tab, you can perform the following operations:
  • Add multiple fields at a time.

    Press and hold Shift, click one or more fields, and drag these fields to the destination section at a time

  • Configure multiple measures at a time.

    You can click the 1 icon next to Columns to modify the display names, description, aggregation methods, value display formats, and text alignment modes of multiple measures at a time. For more information, see Configure a chart.

  • Hide fields.

    You can click the Hide icon on the right of the field you want to hide.

    Both dimensions and measures can be hidden.
    • If you hide a dimension, this dimension is not displayed in the table. However, the values of the dimension can still be used for calculations and grouping.
    • If you hide a measure, this measure is not displayed in the table. However, the values of the measure can still be used to calculate grand totals.
  • Sort fields.
    • You can sort fields in ascending, descending, or custom order. For more information, see Sort data. You can also choose not to sort fields.
    • Sort By Field: You can sort dimensions by measure or by another dimension. Sort fields - 312.1_1
      • You can sort dimensions based on a measure you added on the Data tab.
        In the following figure, order_number is used as an example to show how to sort fields by measure. You can sort fields in one of the following orders: Ascending Ascending, Descending Descending, Ascending Within Group Ascending Within Group, and Descending Within Group Descending Within Group. Added fields
        The difference between Ascending Within Group and Ascending is that the former applies to the data within a group and the latter applies to all data. Example
      • You can sort fields by measure or dimension in your dataset.
      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 both dimensions and measures.
      • Aggregate tables: Advanced Sorting is used to modify or add multiple sorting rules at the same time.
      • Item tables: Advanced Sorting is used to configure the sorting priority.
  • Ranking: The grouped indicators are ranked based on their numerical values. This allows you to easily compare data.
    For more information, see Data ranking. Data ranking
    Note If you want to use the ranking feature, make sure that Pagination is disabled.
  • Configure data percentages in the cross table.
    Select the fields for which you want to configure data percentages, choose Advanced Calculation > Percentage, and configure Percentage based on your business requirements. Percentage

Configure cross table styles

  • In the Basic Information section, configure basic information, including the title and description for the cross table. Basic Information
    Switch Versions: You can switch between different versions. 3
  • In the Display Settings section, configure Custom Table Theme, Hide Column Header, Display Mode, Show Row Numbers, Merge Same Cells, Freeze, Wrap Text, Pagination, Display Indicator Group, and Adjust Column Width.
    • Custom Table Theme: You can select Zebra Line, Wireframe, Simple, or Minimum.

      After you specify a table theme, you also need to configure Major Color and Hide Column Header.

      12
    • Display Mode: You can select Tiled Display or Tree-shaped Display. 13
      If you select Tree-shaped Display, you can customize label names and display levels. 5
      Note If you select Tree-shaped Display, the row dimensions are not hidden.
    • Freeze: If you want to freeze all dimension columns, select Auto (Table Head). 14
    • Pagination: A page can display 10, 20, 30, 50, 80, or 100 data records.
    • You can select Display Indicator Group.
    • You can configure Adjust Column Width.
      • If you select Adapt By Container, the system automatically adjusts the column width.
      • If you select Custom, you must manually adjust the column width. The unit for adjustment is in pixels. The minimum value is 25.
  • In the Functionality Settings section, configure Conditional Format and Enable Field Filter Display.
    • In the Conditional Formatting section, configure conditional formats for data. The following table describes the parameters. 16
      Parameter Description
      Series You can select the fields that you added on the Data tab.
      Shortcut Style You can configure the marker icons or font display colors.
      Custom Style You can customize Text/Background, Icon, Color Scales, and Strip.
      Synchronize Style To You can synchronize the current conditional format to other fields.
    • If you select Enable Field Filter Display, Selected Fields is displayed in the upper-right corner of the cross table.
      You can click Default Indicator, select the fields that are displayed, and click Confirm. Settings
  • In the Totals section, configure the parameters in Columns and Rows. For more information, see How do I configure totals?.

Modify cross table styles

In addition to configuring parameters on the Style tab, you can also perform the following operations to modify the table style:
  • Click the column you want to modify in your cross table. The following configuration options in the drop-down list are displayed. Configure the table style based on your business requirements. 1
  • In addition, you can change the number of data records displayed on each page. 15
  • You can also click and drag to adjust the column width.
  • If you select Enable Field Filter Display in the Functionality Settings section, you can click Selected Fields in the upper-right corner of the table to filter or sort fields.

    You can also drag the fields to adjust the order.

Configure parameters on the Advanced tab

Parameter Option Description
Advanced Settings Auto Refresh After you select Auto Refresh, the system refreshes the latest cross table as scheduled. For example, if you select Auto Refresh, enter 5, and then select Minutes, the system refreshes the latest cross table every 5 minutes.
Filter Interaction After you configure Filter Interaction and click an area or field in the latest cross table, data in other charts that are associated with this table is updated. For more information, see Configure the filter interaction feature.
Hyperlink After you configure Hyperlink, you are directed to the linked report when you click a field in the latest cross table. You can set Hyperlink to Parameter or External Link. If you select Parameter, you must configure global parameters. For more information, see Configure the hyperlink feature.
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. 2

Export data

You can export data only when the Export feature is enabled.
  1. Click the More icon in the upper-right corner of the table and select Export.
  2. In the Export dialog box, configure the following parameters.
    Parameter Description
    Export Name You can customize the name of the exported attachment.
    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 select Export with Format or Pure Data for Cross Table Data.
    • If you want to export more than 50,000 cells of data, we recommend that you use Pure Data.
    • If you need 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.

    Export Channel You can export data to Local or Server.
  3. Click OK.

Delete a chart

  1. Click the More icon in the upper-right corner of the line chart.
  2. Select Delete. The line chart is deleted.

Display secondary indicators

After you configure measure indicators, 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 date 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 that of the indicator.
    • Percent Variance: A percentage is displayed with two decimal places.
  • Calculate the Difference Between Percentages: By default, this parameter is not selected. Unit: pt. After you select Calculate the Difference Between Percentages, if you select Variance and choose Percent Variance as the comparison indicator, pt is automatically displayed, for 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 of indicator to the right of the existing indicator. The column header is named based on the selected comparison method, which can be Compare (Day to Day), Compare (of Last Week), Compare (of Last Month), or Compare (of Last Year).
      If the existing indicator is a column header, the secondary indicator is displayed to the right side of the existing indicator. 1
    • Right: The secondary indicator is displayed separately on the right side of the existing indicator.
      • If the existing indicator is a column header, the last column dimension is split to show 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 existing indicator is a row header, the last row dimension is split to display the results of Compare (Day to Day) and Compare (of Last Week), which is similar to the scenario in which the existing indicator is a column header. The difference is that the secondary indicator is displayed below the existing indicator, instead of to the right side of the existing indicator.
    • Below Indicator: You can add multiple columns of comparison indicators below the existing indicator. The alignment of the added indicators is the same as the alignment of the existing indicator. The text is aligned to the left and the values are aligned to the right.
      The added indicators share the same cell as the existing indicator. You can add a row below the header name of the existing indicator. The row is named based on the selected comparison method, which 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 language you use. 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 set the display style, the configured color remains unchanged no matter what language you switch to.
  • Synchronize Style To: You can synchronize the configurations of the current indicator to other N indicators.

How do I configure totals?

Notes:
  • If your original data is GMV (SUM), the default calculation method is Automatic. In this case, the logic 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 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 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 used to calculate totals is id (COUNT (DISTINCT)).
What is the logic for calculating the rate?
  1. Define the rate.

    In this example, rate is calculated by using the following formula: Rate = Indicator A/Indicator.

  2. Add a measure 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 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, the value of the calculated result may be more than 100%. In this case, the calculation result cannot be trusted.