Function used: length(extractAll(target_str, char))
Explanation: target_str is the string to be analyzed, char is the specified character
Example: Given a string "aaabc", to count the number of times the character "b" appears, you can use the countif function in Excel.
Here you can write it like this: length(extractAll('aaabc', 'b'))
The returned result is a numeric value. In this case, the result is 1.

2.1 Calculate the percentage of the total amount #

Function: TOTAL( ) along ( )
Meaning: Calculate the total of the metric based on the dimension. This function is typically used to calculate the percentage of the total amount.
In the following example, the function calculates the total payment amount for each province based on the province dimension. This prepares for the subsequent calculation of each city's payment amount as a proportion of the province's total payment amount.
Function example: TOTAL(sum([payment amount])) along([city]), which calculates the total payment amount for all cities. As shown in the figure, when other dimensions (provinces) exist 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 payment amount to the province's total payment amount.

2.2 Rolling sum based on dimension #

Function: RUNNING_SUM( ) along( )
Meaning: Rolling sum based on dimension
Example: RUNNING_SUM(sum([payment amount])) along([city]), which performs a rolling sum based on city. As shown in the figure, 118,578 is the sum of payment amounts for Lincang and Lijiang, and 174,599 is the sum for Lincang, Lijiang, and Baoshan. When other dimensions (provinces) exist in the chart, the rolling sum of payment amounts for all cities under each province is calculated.

Extension: In addition to the RUNNING_SUM expression, you can also use RUNNING_AVG, RUNNING_MIN, and RUNNING_MAX to perform rolling calculations for average, minimum, and maximum values. Example: RUNNING_AVG(sum([payment amount])) along([city])

This function cannot be found via search, but it can be used directly.

2.3 Calculate ascending and descending percentiles of metrics #

Function: RANK_PERCENTILE( , 'asc') along( )
Meaning: Calculate the ascending percentile of the metric based on the dimension (replace asc with dsc to calculate the descending percentile)
Example: RANK_PERCENTILE(sum([Payment Amount]),'asc') along([City]), which calculates the percentile rank of each city's payment amount in ascending order. When other dimensions (such as province) are present in the chart, the percentile rank of each city's total payment amount is calculated within each province.

2.4 Calculate the average of all metric values within a specified window based on dimensions #

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

Extension: In addition to the WINDOW_AVG expression, you can also use WINDOW_SUM, WINDOW_MIN, and WINDOW_MAX to calculate the sum, minimum, or maximum within the window.
Common scenario: When viewing data trends with significant fluctuations, WINDOW_AVG can be used to smooth the curve and serve as a trend line.

2.5 Calculate the metric value at the position plus offset based on dimensions #

Function: LOOKUP( , offset) along( )
Meaning: Based on the dimension, retrieves the metric value at the current position plus the offset. If the offset is -1, it retrieves the metric value at the previous position.
Example: LOOKUP(sum([Payment Amount]),-1) along([Payment Date]), which displays the total payment amount for the previous day.