1. Overview
1.1 Application scenarios
For example, calculate the sum of the order amounts of different ship methods in every year.
Combine with other quick calculation functions to calculate the proportion within the group, the accumulated proportion within the group, and so on.
1.2 Implementation ideas
Use the TOTAL function.
2. Concept
| Grammar | total(x_agg(array), range, agg) | Perform cross-row summary calculation of indicators based on the added fields on the horizontal and vertical axis or row and column dimensions. |
|---|---|---|
| Parameter1 | x_agg(array) | The first parameter is the indicator calculated by the user, which must be an aggregate function or aggregate indicator. |
| Parameter2 | range | The second parameter range is the calculation range set by the user, 0 means summarizing all rows, and 1 means summarizing all rows in the group. |
| Parameter3 | agg | The third parameter agg is the calculation rule of the summary, "sum" is the sum; "avg" is the average, "max" is the maximum value, and "min" is the minimum value. |
Note: The third parameter supports SUM, AVG, MAX, MIN, and is not case sensitive.
3. Examples
3.1 Calculate the summary value within the group
1) Calculate the aggregate value of the "AMOUNT" of different ship methods in every year, for example, calculate the aggregate value of the amount in the group of year 2008, as shown in the following figure:
For the writing of functions, please refer to: Function Calculation Format.
3.2 Calculate the proportion within the group
Continue to calculate the "proportion within the group" to calculate the proportion of the amount of different ship methods in the same year: SUM_AGG(AMOUNT)/TOTAL(SUM_AGG(AMOUNT),1,"sum") , as shown in the following figure:
Formula description:
| Formula | Description |
|---|---|
| SUM_AGG(AMOUNT) | The amount summarized in the group according to the year and "SHIPMETHOD". |
| TOTAL(SUM_AGG(AMOUNT),1,"sum") | Calculate the sum of the amount of all ship methods in the same year. |
| SUM_AGG(AMOUNT)/TOTAL(SUM_AGG(AMOUNT),1,"sum") | Annual amount of each ship method/ summary value of amount of all ship methods in the current year. |
For the writing of functions, please refer to: Function Calculation Format.
3.3 Calculate the accumulated proportion within the group
Calculate the "accumulated proportion within the group", enter the formula: ACC_SUM(SUM_AGG(AMOUNT),1)/TOTAL(SUM_AGG(AMOUNT),1,"sum"), as shown in the figure below:
Formula description:
| Formula | Description |
|---|---|
| ACC_SUM(SUM_AGG(AMOUNT),1) | Calculate the accumulated value of the amount of different ship methods in the same year. |
| TOTAL(SUM_AGG(AMOUNT),1,"sum") | Calculate the sum of the amount in the same year |
| ACC_SUM(SUM_AGG(AMOUNT),1)/TOTAL(SUM_AGG(AMOUNT),1,"sum") | accumulated value/summary value |
4. Notice
If you calculate the summary value of the "AMOUNT" in the self-service dataset, you can add a column and select "All Values/Within the group" to set the sum of total amount, as shown in the figure below:
Since the data here is a calculation of detailed data, the result will not be changed due to dimensional changes. The results are as follows:
Previous：SAME_PERIOD
