This document introduces some common expressions and classic usage examples of table calculation functions—such as the Pareto chart—which you can reference to build your own table calculation functions.

Table calculation refers to performing secondary calculations on already generated visualization charts. After the underlying data has been summarized, table calculations can be used to perform additional operations on the data.
To perform secondary calculations, table calculations can be used to omit steps such as downloading, exporting, and then further processing in Excel. For example, summing metrics, moving calculations, and more.
The product currently supports configuring table calculations visually. In addition, you can also add fields and write table calculation functions to achieve this. This document will introduce table calculation functions. At the same time, it will introduce a typical application scenario for table calculation functions: the Pareto chart.

TOTAL( ) along ( ) #

Meaning: Calculate the total value of the metric for each dimension. This function is usually used in scenarios where the percentage of the total is calculated.
In the following example, the function is used to calculate the total payment amount for each province. This prepares for the subsequent calculation of the proportion of [the payment amount of each city] to [the total payment amount of the province].
Function example: TOTAL(sum([Payment Amount])) along([City]), which calculates the total payment amount for all cities. As shown in the figure, when there are other dimensions (such as provinces) in the chart, the total payment amount for all cities under each province is calculated.

Total percentage: Taking the above example, calculating sum([Payment Amount])/TOTAL(sum([Payment Amount])) along([City]) yields the proportion of each city's total payment amount to the province's total payment amount.

RUNNING_SUM( ) along( ) #

Meaning: Perform a running sum for each dimension
Example: RUNNING_SUM(sum([Payment Amount])) along([City]), which performs a running sum for each city. As shown in the figure, 118,578 is the sum of the payment amounts for Lincang and Lijiang, and 174,599 is the sum of the payment amounts for Lincang, Lijiang, and Baoshan. When other dimensions (such as provinces) are present in the chart, calculate the running total of payment amounts for all cities within each province.

Extension: In addition to the RUNNING_SUM expression, you can also use RUNNING_AVG, RUNNING_MIN, and RUNNING_MAX to calculate running averages, minimums, and maximums. Example: RUNNING_AVG(sum([Payment Amount])) along([City])

RANK( ,'asc'/'desc') along( ) #

Meaning:
Returns the unique rank of the current row within the partition. Assigns the same rank to identical values, allowing ties.
Use the optional "asc" or "desc" parameter to specify ascending or descending order. The default is descending order.
Example:
RANK(sum([profit]), 'asc') along([region]) Using this function, you can rank the sum of profits by region in ascending order. Tied ranks are allowed and the next rank is skipped. If the sales amounts are (6, 7, 7, 9), the ranking results are (1, 2, 2, 4).

The query results are as follows:

RANK_PERCENTILE( ,'asc') along( ) #

Meaning: Based on the dimension, calculates the ascending percentile of the metric (replace asc with dsc to calculate the descending percentile).
Example: RANK_PERCENTILE(sum([Payment Amountent Amount]),'asc') along([City]) This calculates the percentile of payment amounts for each city from lowest to highest. When other dimensions (such as provinces) are present in the chart, calculate the percentile of each city's total payment amount within each province.

WINDOW_AVG( ,start,end) along( ) #

Meaning: Window function. Based on the dimension, calculates the average of metric values within the window from start to end. For example, if start is -1 and end is 1, it calculates the average of three values along the dimension, from the previous to the next window.
Example: WINDOW_AVG(sum([Payment Amount]),-1,1) along([Payment Date]) Based on the payment date, calculates the average payment amount from the previous day to the next day for each day.

Extension: In addition to the WINDOW_AVG expression, you can also use WINDOW_SUM, WINDOW_MIN, and WINDOW_MAX to calculate window sums, minimums, and maximums.
Common scenarios: When viewing data trends with large fluctuations, use WINDOW_AVG to smooth the curve and serve as a trend line.

LOOKUP( , offset) along( ) #

Meaning: Based on the dimension, retrieves the metric value at the current position plus the offset. For example, if the offset is -1, it retrieves the metric value from the previous position.
Example: LOOKUP(sum([Payment Amount]),-1) along([Payment Date]) Displays the total payment amount from the previous day.

This section provides an example that cannot be implemented directly through the table calculation configuration in the analysis module and requires the use of table calculation functions.

Pareto chart #

Example result #

A Pareto chart consists of a bar chart and a line chart, where the bars display metric values in descending order, and the line shows the cumulative percentage. Using the sales amount of each category as an example, a Pareto chart can be used to display the cumulative proportion of top product categories and identify key categories.

Steps #

1. For example, to create a Pareto chart of sales amount by subcategory: Select the dual-axis chart type, set subcategory as the dimension, sales amount as the primary axis, to create the subcategory-sales amount chart.

2. Use the sorting feature in the analysis module to sort the sales amount in descending order.

3. Create a cumulative percentage field with the following expression: running_sum(sum([Payment Amount])) along ([product_type])/total(sum([Payment Amount])) along ([product_type]). This combines two table calculation functions. The running_sum function calculates the rolling sum of sales amount by subcategory, divided by the total sales amount of all subcategories, resulting in the cumulative proportion up to each subcategory.

4. Drag the cumulative percentage field to the secondary axis of the chart to form the Pareto chart. Use the chart configuration on the left to adjust the axis data format and set the secondary axis data format to percentage.