Table Calculation

1. Overview

1.1. Application Scenarios

In the process of data analysis, sometimes charts generated from raw data may not be able to meet deeper and more complex analysis needs. We can use table calculations to further process and analyze aggregated data to meet more diverse analysis demands. For example, you may want to perform ranking analysis on aggregated data, or analyze the average sales of each region in the past 7 days, etc.

1.2. Feature Introduction

Table calculation does not require users to write complex formulas or use custom calculated fields. Through intuitive visual configuration, various common advanced calculations and nested calculations between calculations can be quickly realized.

In the value bar, click the field to which you want to add a table calculation, and select "Advanced Calculation - Table Calculation" from the dropdown menu to pop up the table calculation setting box.

Table calculation is mainly divided into two steps: determine the calculation type and determine the calculation basis.

• Calculation type: Select the calculation type suitable for the current analysis needs. Seven methods are provided, including difference, difference percentage, percentage, proportion, cumulative, moving calculation, and ranking.

• Calculation basis: Clarify whether to compare within the entire table or analyze within a specific group, that is, determine the data range for calculation.

2. Calculation Types

There are 7 calculation types for table calculation: difference, difference percentage, percentage, proportion, cumulative, moving calculation, and ranking. Details are as follows:

2.1. Difference

Used to calculate the difference between the current value and another value in the table. Formula: Difference = Current Value - Comparison Value

Supports calculating the difference based on the first/last or previous/next value in the selected area, and supports horizontal or vertical comparison calculation.

First	Calculate the difference between the current value and the first value in the partition.
Previous	Calculate the difference between the current value and the previous value in the partition.
Next	Calculate the difference between the current value and the next value in the partition.
Last	Calculate the difference between the current value and the last value in the partition.

For example, to calculate the difference in "Sales Quantity" for each month in 2020 compared to the beginning of the year and the month-over-month fluctuation of sales quantity each month, the detailed steps are as follows:

1. Create a normal table, drag "Date-Month" into the dimension bar, and "Sales Quantity" into the value bar.

2. In the value bar, set "Sales Quantity" to Advanced Calculation - Table Calculation; select Difference as the calculation type, and select the first and previous as the basis for difference calculation; select single column as the calculation area, and downward as the addressing direction.

• Difference compared to the beginning of the year: Since the original table data range is 2020, you can select "relative to the first" to calculate the difference between each month's data and January's data, achieving the difference compared to the beginning of the year.

• Difference compared to the previous month: Select "relative to the previous" to calculate the difference between each month's data and the previous month's data, achieving the analysis of month-over-month fluctuation of sales quantity each month.

The calculation area determines the calculation range in the current view. Since this scenario is a single-dimension analysis with no comparison dimension, the calculation results for single column and entire table are the same. The calculation area and addressing direction will be described in detail in Section 3 of this document.

3. Click OK to complete the addition of the difference table calculation.

As you can see, when compared to the previous month, since January 2020 is already the first value, the difference calculation result is empty.

2.2. Difference Percentage

Used to calculate the difference percentage between the current value and another value in the table. Formula: Difference Percentage = (Current Value - Comparison Value) / Comparison Value

Supports calculating the difference based on the first/last or previous/next value in the selected area, and supports horizontal or vertical comparison calculation.

First	Calculate the difference between the current value and the first value in the partition.
Previous	Calculate the difference between the current value and the previous value in the partition.
Next	Calculate the difference between the current value and the next value in the partition.
Last	Calculate the difference between the current value and the last value in the partition.

For example, to calculate the month-over-month fluctuation of "Sales Quantity" for each region in each month of 2020, the detailed steps are as follows:

1. Create a normal table, drag "Date-Month" into the dimension bar, "Region" into the comparison bar, and "Sales Quantity" into the value bar.

2. In the value bar, set "Sales Quantity" to Advanced Calculation - Table Calculation; select Difference Percentage as the calculation type, relative to "previous", select single column as the calculation area, and downward as the addressing direction.

The calculation area determines the calculation range in the current view, and the addressing direction determines the calculation direction. In this scenario, the dimension field is date, and the comparison field is region. To calculate the difference percentage for each region, you need to partition by region and then calculate downward in a single column. The calculation area and addressing direction will be described in detail in Section 3 of this document.

3. Click OK to complete the addition of the difference percentage table calculation.

For example, for the East China region, this calculates the month-over-month growth rate of sales quantity in the East China region;

2.3. Percentage

Used to calculate the ratio between the current value and another value in the table. Formula: Percentage = Current Value / Comparison Value

Supports calculating the difference based on the first/last or previous/next value in the selected area, and supports horizontal or vertical comparison calculation.

First	Calculate the difference between the current value and the first value in the partition.
Previous	Calculate the difference between the current value and the previous value in the partition.
Next	Calculate the difference between the current value and the next value in the partition.
Last	Calculate the difference between the current value and the last value in the partition.

For example, this table shows the total sales of a store in each month of 2020. You can use percentage calculation to calculate the completion rate of sales in the current month compared to the previous month.

1. Create a normal table, drag "Date-Month" into the dimension bar, "Region" into the comparison bar, and "Sales Amount" into the value bar.

2. In the value bar, set "Sales Amount" to Advanced Calculation - Table Calculation; select Percentage as the calculation type, relative to "previous", select single column as the calculation area, and downward as the addressing direction.

In this scenario, the dimension field is date, and the comparison field is region. To calculate the completion rate of sales in the current month compared to the previous month, you need to partition by region and then calculate downward in a single column. The calculation area and addressing direction will be described in detail in Section 3 of this document.

3. Click OK to complete the addition of the percentage table calculation.

For example, for the East China region, in February 2020, 49.5% of the sales in January 2020 were completed; in March 2020, 206.05% of the sales in February 2020 were completed.

2.4. Proportion

Used to calculate the proportion of the current value to the total value of the indicator in the selected area. Formula: Proportion = Current Value / Total Value of Indicator in Selected Area

Can be calculated by single column, single row, entire table, or by area. In this calculation type, the addressing direction is meaningless.

Difference between proportion and percentage:

• Proportion usually refers to the ratio of a part to the whole. It can be used to describe the proportion of a category in the total.

• Percentage is a unitless mathematical concept used to indicate how many percent one number is of another.

For example, this table shows the total sales of a store in each month of 2020. You can calculate the proportion of sales in each region in the current month to the total sales of that region or to the total sales of all regions.

Scenario 1: Calculate the proportion of sales in each region in the current month to the total sales of that region:

1. Create a normal table, drag "Date-Month" into the dimension bar, "Region" into the comparison bar, and "Sales Amount" into the value bar.

2. In the value bar, set "Sales Amount" to Advanced Calculation - Table Calculation; select Proportion as the calculation type.

3. Since you need to calculate the proportion of sales in each region to the total sales of that region, select "Area" as the calculation area, row depth 0, column depth 1. This means partitioning by region and calculating the proportion for all months in 2020 in each region. The sum of proportions in each region is 100%.

4. Click OK to complete the addition of the proportion table calculation. For example, in the East China region, the sales in February 2020 accounted for 5.06% of the total sales in that region.

Scenario 2: Calculate the proportion of sales in each region in the current month to the total sales of all regions.

Since you need to calculate the proportion of sales in each region to the total sales of all regions, select "Entire Table" as the calculation area. This means calculating the percentage of each region's sales in each month to the total sales of all regions.

Click OK to complete the addition of the proportion table calculation. For example, in the East China region, the sales in February 2020 accounted for 3.58% of the total sales of all regions.

2.5. Cumulative

Used to calculate the cumulative value of indicators in the selected area in order, supporting four calculation types: sum, average, maximum, and minimum.

• Sum: Each value is added to the previous value.

• Average: The current value and all previous values are averaged.

• Minimum: All values are replaced with the minimum value in the original partition.

• Maximum: All values are replaced with the maximum value in the original partition.

For example, this table shows the monthly sales of each region in a store in 2020.

Scenario 1: Calculate the monthly cumulative sales of each region

1. Create a normal table, drag "Date-Month" into the dimension bar, "Region" into the comparison bar, and "Sales Amount" into the value bar.

2. In the value bar, set "Sales Amount" to Advanced Calculation - Table Calculation; select Cumulative - sum as the calculation type.

3. Since you need to calculate the monthly cumulative sales of each region, select "Single Column" as the calculation area, and downward as the addressing direction. This means partitioning by region and calculating the cumulative sum of monthly sales in each region. As shown in the figure, green is the calculation area, and the values are added vertically one by one.

Scenario 2: Calculate the cumulative sales of each month

Since you need to calculate the cumulative sales of each month, select "Single Row" as the calculation area, and crosswise as the addressing direction. This means partitioning by month and calculating the cumulative sum of sales in each region for each month. As shown in the figure, green is the calculation area, and the values are added horizontally one by one.

2.6. Moving Calculation

Moving calculation (rolling calculation) is often used to observe long-term trends. For example, for securities data, the market fluctuates every day, and it is difficult to grasp the overall situation through daily ups and downs. Moving calculation can be used to define a series of values to be aggregated by the selected aggregation.

Used to calculate the aggregation result of a certain item in the group and its previous and next items. Supports four calculation types: sum, average, maximum, and minimum.

Window: The calculation window for moving calculation of indicators. You can adjust the calculation range by setting the window size. The window calculation includes the current value by default. When the calculation range is 0 before or after, you can configure whether the calculation window "includes the current value".

For example, the following figure shows the monthly sales of each region in 2020. You can use moving calculation to obtain the average sales of each region in the past three months.

1. Create a normal table, drag "Date-Month" into the dimension bar, "Region" into the comparison bar, and "Sales Amount" into the value bar.

2. In the value bar, set "Sales Amount" to Advanced Calculation - Table Calculation; select moving calculation - sum as the calculation type.

3. Since you need to calculate the average sales of each region in the past three months:

• Select the calculation range as the previous 2 and next 0, which means the window is the current month and the previous two months;

• Select "Single Column" as the calculation area, and downward as the addressing direction. This means partitioning by region and calculating the average sales of the past three months in each region.

4. As shown in the figure, for January 2020, since there are no previous two values, the result is the average value of January; for February 2020, since there is no previous value, the result is the average value of January and February, and so on; when the sales value of a certain month is null, it is skipped. As shown in the data for August below, it is the average value of June and August, skipping July for calculation.

2.7. Ranking

Used to rank indicators in the selected area.

Provides two types: ascending and descending. Ascending ranks values from lowest to highest, and descending ranks values from highest to lowest.

Sorting method: When there are equal indicator values, you need to choose the appropriate sorting method according to the actual scenario

• Competitive sorting - RANK (1, 2, 2, 4): Assigns the same rank to equal values.

• Dense - DENSE_RANK (1, 2, 2, 3): Duplicate values have the same rank, and the next number in the ranking sequence.

• Unique - ROW_NUMBER (1, 2, 3, 4): Assigns different rankings to duplicate values according to the ranking direction.

Note: Ranking does not support secondary calculation.

For example, the following figure shows the monthly sales of each region in 2020.

Scenario 1: Calculate the monthly sales ranking of each region

1. Create a normal table, drag "Date-Month" into the dimension bar, "Region" into the comparison bar, and "Sales Amount" into the value bar.

2. In the value bar, set "Sales Amount" to Advanced Calculation - Table Calculation; select ranking - descending as the calculation type.

3. Since you need to calculate the monthly sales ranking of each region (from high to low), select "Single Column" as the calculation area.

4. As shown in the figure, for the East China region, the monthly sales of the East China region are ranked from high to low, and the specific ranking of each month is displayed.

Scenario 2: Calculate the sales ranking of each region in each month

Since you need to calculate the sales ranking of each region in each month (from high to low), select "Single Row" as the calculation area.

4. As shown in the figure, for January 2020, the sales of each region are ranked from high to low, and the specific ranking of each region is displayed.

3. Calculation Area and Addressing Direction

The calculation area is the partition, which defines the calculation range for table calculation. Each partition is an independent "table", and each calculation is performed in an independent table. The tables do not affect each other. The calculation area includes single row, single column, entire table, and area.

Addressing specifies the direction of table calculation in each partition, including downward, crosswise, downward then crosswise, crosswise then downward.

The calculation area and addressing are cascading configurations, and the specific rules are as follows:

Calculation Area/Addressing Direction	Downward	Crosswise	Downward then Crosswise	Crosswise then Downward
Single Column	✅	❎	❎	❎
Single Row	❎	✅	❎	❎
Entire Table	❎	❎	✅	✅
Area	✅Only dimension	✅Only comparison	✅Both dimension and comparison	✅Both dimension and comparison

1. Single Column Downward: Calculate vertically in each column, and recalculate for each column.

2. Single Row Crosswise: Calculate horizontally in each row, and recalculate for each row.

3. Entire Table Downward then Crosswise: Calculate vertically from top to bottom in each partition, then jump to the first position of the next column and start calculating from top to bottom until the last data in the partition is finished, as shown below.

4. Entire Table Crosswise then Downward: Calculate in a Z-shape, first horizontally from left to right, then jump to the first position of the next row and start calculating from left to right until the last data in the partition is finished, as shown below.

5. Area

Area-based calculation refers to subviews or sub-tables in the view. When calculating, the "area" is not as extensive as the "table", but is calculated independently in each group, and different areas are recalculated.

Partition and Addressing: Four cases, details as follows:

Applicable Scenario	Addressing Direction	Specific Performance
Number of dimension fields ≥1 and number of comparison fields = 0	Downward	Calculate from top to bottom in each area
Number of comparison fields ≥1 and number of dimension fields = 0	Crosswise	Calculate from left to right in each area
Number of dimension fields ≥1 and number of comparison fields ≥1	Downward then Crosswise	Calculate in each area, first vertically from top to bottom, then jump to the first position of the next column and start calculating from top to bottom until the last data in the area is finished.
	Crosswise then Downward	Calculate in each area in a Z-shape, first horizontally from left to right, then jump to the first position of the next row and start calculating from left to right until the last data in the area is finished.
Number of dimension fields = 0 and number of comparison fields = 0	Table calculation requires dimension or comparison fields to determine the calculation basis, so this scenario is not supported

Depth: Includes row depth and column depth. The depth value N is a natural number, with values of 0, 1, 2...N (N = number of fields in the dimension or comparison bar), default is 0

• Column depth: Determines the comparison dimension for partitioning. Column depth 0 means no grouping by column dimension; column depth 1 means grouping by the first column dimension; column depth 2 means grouping by the second column dimension, and so on.

• Row depth: Determines the row dimension for partitioning. Row depth 0 means no grouping by row dimension; row depth 1 means grouping by the first row dimension; row depth 2 means grouping by the second row dimension, and so on.

As shown below, row depth = 0 means calculating for the entire table by column from top to bottom; row depth = 1 means recalculating for each region by the first dimension; row depth = 2 means recalculating for each month by the second dimension. Since there are only two dimensions, the calculation result is itself (the calculation result of proportion type is always 1)

4. Secondary Calculation

Except for ranking calculation, all other calculation types can be nested on the result of a previous calculation, i.e., table calculations can be nested with each other.

For example, to calculate the cumulative revenue multiple of each store each year, you need to first calculate the cumulative amount by period for each store each year, and then calculate the ratio to the first period amount, which is the cumulative revenue multiple.

Cumulative Revenue Multiple: Indicates the growth of cumulative revenue relative to the first period revenue from the first period.

1. Create a normal table, drag "Store" and "Year" into the dimension bar, "Period" into the comparison bar, and "Sales Amount" into the value bar.

2. In the value bar, set "Sales Amount" to Advanced Calculation - Table Calculation; select cumulative - sum as the calculation type, calculation area = single row, addressing direction = crosswise, as shown below. At this point, the cumulative sales amount by period for each store each year is calculated.

   

3. Check to perform a secondary table calculation on the calculation result, select percentage as the calculation type, relative to the first, calculation area = single row, addressing direction = crosswise, as shown below:

   

4. At this point, the cumulative revenue multiple of each store each year is obtained.

   

5. Null Value Handling Logic

The handling logic for null values in table calculation is as follows:

Calculation Type	Handling Logic and Description	Example Description
Difference	When the comparison value is null, the calculation result is null	For example, if the first value in a calculation area is null, all calculation results are null
Difference Percentage
Percentage
Proportion	null/total = null	When the current value is null, proportion = null/total = null
Cumulative	When a value is null, skip the value and continue accumulating downward	If the original values are 10, null, 20, the cumulative values are 10, null, 30
Moving Calculation		If the original values are 10, null, 20, 30 and the window is previous two, next zero - sum, the result values are: 10, 10, 30, 50
Ranking	null is the minimum value	If the original values are [null, 2, -1, 3] Ascending: [null, -1, 2, 3]; Descending: [3, 2, -1, null]

In comparison scenarios, there may be null values, which are skipped during calculation. For example:

The original data is North China - February, South China - January. Since it is a two-dimensional table, it may appear that North China - January and South China - February have values.

During calculation, null values are skipped. For example, for average calculation, the actual calculation is 20/2;

In this scenario of missing dates, if you want to calculate in date order, it is recommended to supplement the dates in the database in advance.

	January	February
North China		10
South China	10

Note: For average calculation involving null values, null values are not counted in the denominator and are skipped.

6. Usage Restrictions

Table calculation is not supported for detail tables