Sometimes, charts created using dimension and metric fields are not sufficient to support deeper analytical needs. Table calculation enables further computation and analysis on displayed charts to meet a wider range of scenarios.

2.1 Table calculation for tables/pivot tables #

Step 1: Select the calculation type to analyze, such as percentage of total. Each calculation type will be described in detail in the feature introduction.

Step 2: Confirm the metric for calculation. Table calculation must be performed on a single metric.

Step 3: Determine the calculation area and addressing direction to define the calculation range and direction.

Step 4: Using percentage of total for the entire table as an example, click Apply to generate the calculation result. Percentage of total creates a virtual field in the metrics column, showing the proportion of each item in the entire table. You can click to adjust the field name, format, and other settings.

2.2 Table calculation for charts #

Step 1: Select the calculation type for analysis, such as aggregation, then select sum.

Step 2: Confirm the metric for calculation.
Step 3: Confirm the calculation area and addressing direction. If the calculation area is set to the legend, the calculation is performed on the metric values represented by the bars on the horizontal axis.

Step 4: Click Apply to generate the calculation result. For example, when using aggregation with sum, a table calculation field is generated showing the cumulative sum of the metric up to each item.

3.1 Calculation types #

3.1.1 Difference #

Effect: Calculates the difference in the value of the same metric across different dimension items. Supports calculating the difference based on the first or last item in the selected area, or the previous or next calculation unit, and supports horizontal or vertical comparison calculations.

3.1.2 Relative to #

Effect: Compares the current value with the first, last, previous, or next item in the calculation area.

3.1.3 Percentage difference #

Effect: Calculates the percentage difference of the same metric across different dimension items. Supports calculating the difference based on the first or last item in the selected area, or the previous or next calculation unit, and supports horizontal or vertical comparisons.

3.1.4 Percentage of total #

Effect: Calculates the percentage of the metric value relative to the total within the selected calculation area. The percentage of the metric can be calculated by column, row, entire table, or by dimension. When the calculation type is percentage of total, the addressing direction is not relevant.
As shown in the figure, by calculating the percentage of total by partition, the percentage of payment amount for each order type under each secondary channel within each primary channel can be obtained.

3.1.5 Cumulative total #

Effect: Calculates the cumulative value of the metric in the selected calculation area in sequence. Supports four calculation types: sum, average, maximum, and minimum.
As shown in the figure, calculating the cumulative sum by column yields the daily cumulative payment amount for each order type in January.
Taking the figure below as an example, after applying the calculation, the results are D=A+B and E=A+B+C.

In the chart, the cumulative payment amount for each day can also be visually displayed by cumulative summation.

3.1.6 Percentile #

Effect: Displays the percentile value of the metric within the selected area. If ascending order is set, the maximum value is the 100th percentile; if descending order is set, the minimum value is the 100th percentile.
As shown in the figure, calculating the percentile of metric values by column yields the percentile of payment amounts for each order type under each secondary channel.

3.1.7 Ranking #

Effect: Ranks the metric values within the selected area.
As shown in the figure, in ascending order, the city with the lowest payment amount is ranked first, while in descending order, the city with the highest payment amount is ranked first.

In the chart, you can also view the daily ranking changes of orders in each status. At this point, you need to change the calculation region unit to the dimension axis tick, meaning to calculate the ranking of each data point on each axis.

When indicator values are equal
Competitive ranking: When several items have equal indicator values, they are ranked forward; this is commonly used.
As shown in the figure, Wuxi and Xuzhou are tied for 6th place, Shantou and Foshan are tied for 8th place.

Adjusted competitive ranking: When several items have equal indicator values, they are ranked backward.
As shown in the figure, Wuxi and Xuzhou are tied for 7th place, Shantou and Foshan are tied for 9th place.

Dense: When several items have equal indicator values, they are ranked forward, and subsequent rankings continue in order according to the ranking number, regardless of the number of items sharing the rank.
As shown in the figure, Wuxi and Xuzhou are tied for 6th place, Shantou and Foshan are tied for 7th place.

Unique: Each item is assigned a unique ranking; even if the indicator values are the same, the rankings will differ.
As shown in the figure, Wuxi and Xuzhou have the same payment amount, Shantou and Foshan have the same payment amount, but their rankings are different.

3.1.8 Moving calculation #

Result: Within the selected area, perform interval calculations for each indicator value.
As shown in the figure, the moving calculation computes the average of the previous, current, and next positions, resulting in A1 being the average of B, C, and D; for data at the ends of the table, such as A2, since there is no previous value, the result is the average of B and C. Four calculation methods are supported: sum, average, maximum, and minimum.

In the chart, you can also use moving calculation to compute the average value of the most recent 30 days for each day.

After obtaining the moving average curve for the most recent 30 days, click the original indicator label to hide the field. In this way, the annual indicator fluctuations can also be smoothed, resulting in a more intuitive trend change.

Window
This refers to the calculation window for the indicator moving calculation. After configuring the window, the sum, average, maximum, or minimum of each metric will be calculated for a range spanning from n values before to n values after each point.

3.2 Calculation unit area and addressing direction #

3.2.1 Single column/single row #

Calculation is performed by column or row, and each column or row is calculated separately, as shown in the figure.
At this time, the addressing direction only supports downward or to the right.

3.2.2 Entire table #

Table calculation is performed for the metric across the entire table, and the addressing direction can be set to downward then across, or across then downward.
Taking downward then across as an example, the calculation direction is as shown in the figure below. According to the configuration in the figure, A1 = C - B, A2 = D - C

3.2.3 Area #

The calculation area is grouped by configuring column depth and row depth.
As shown in the figure below, to calculate the proportion of payment amount for secondary channels under each primary channel and order type, the table area needs to be partitioned by primary channel and order type, and then the metric proportion is calculated within each area. Therefore, select the table calculation type as total percentage, and set the calculation unit area as 'area'.
Select row and column depth based on partition dimensions: In this case, the calculation area needs to be divided by primary channel (the first row dimension) and order type (the first column dimension), so both row depth and column depth are set to 1. Click Apply to get the result.
In the figure below, the sum of the percentages in each yellow block is 100%.
What do column depth and row depth mean?
Column depth: Determines the column dimension used for partitioning. If the column depth is 0, there is no grouping by column dimension; if the column depth is 1, grouping is by the first column dimension; if the column depth is 2, grouping is by the second column dimension, and so on.
Row depth: Determines the row dimension used for partitioning. If the row depth is 0, there is no grouping by row dimension; if the row depth is 1, grouping is by the first row dimension; if the row depth is 2, grouping is by the second row dimension, and so on.
After selecting different column and row depths, one calculation unit area will be highlighted in yellow on the table to help with understanding and for reference.

3.3 Secondary table calculation #

Starting from version V2.58.0, the "Table Calculation" feature supports secondary table calculation. Users can configure a table calculation again on the result of the current table calculation, based on the initial configuration. The specific configuration includes: calculation type, relative to, unit calculation region, addressing direction, and more.

3.4 Quick table calculation #

(1) Operation path
For the "7-day moving average" of the table calculation feature, the system supports quick configuration. You can use Quick Table Calculation to improve operational efficiency. The operation path is as follows:
Step 1: Configure the relevant measures on the visual query page.
Step 2: Click the dropdown button on the left side of the measure dimension.
Step 3: Click Quick Table Calculation - 7-day moving average.

(2) 7-day moving average
For the configuration of the 7-day moving average, refer to the following steps:
Operation condition: There is a date field in the dimension bar.
Step 1: Click the "7-day moving average" button.
Step 2: Add a table calculation configuration to the current measure and generate the corresponding derived measure capsule. Refer to the following content to complete the configuration.

• Calculation type: Moving calculation. Average value
• Window: Previous 6, next 0. Select "Show empty result if window size is insufficient"
• Calculation measure: Current measure capsule
• Unit calculation region: The default value of "unit calculation region" when creating a table calculation in the current chart.
• Addressing direction: The default value of "addressing direction" when creating a table calculation in the current chart.
• Do not enable secondary table calculation.

(3) Special cases
When using a pivot table for quick table calculation, if there is only one dimension in the row or column, the above situation also applies. Unlike the above default values, if the date dimension is in the row, the unit calculation region defaults to single column; if the date dimension is in the column, the unit calculation region defaults to single row.