Overview
SUM_AGG function is used to summarize and add up data for the specified dimension (dragged into the analysis column). The calculation field will make dynamic adjustment automatically when you switch the analysis dimension.
| Grammar | Parameter | |
|---|---|---|
| Description | SUM_AGG(array) | array |
| Return the summary sum value of the indicator field according to the current analysis dimension. The result generated is a data column with the same number of rows as that of current analysis dimension. | The result must be returned by a non-aggregate function formula. It can be the calculation result of an indicator field/dimension and a regular formula. |
Notes
Using SUM_AGG Function to Calculate the Average
SUM_AGG is generally used for summation after detail filtering and division after summation. If you only need to sum up a single value, you can drag and drop the field to the indicator. It will automatically sum up.
If the dimension field in the lateral axis is Day, the value returned by the calculation field SUM_AGG(sales) in the vertical axis is the total sales for each day.
If the dimension field in the lateral axis is Month, the value returned by the calculation field SUM_AGG(sales) is the total sales for each month.
For example, you have obtained the contract amount and purchase quantity for each year from 2011 to 2017 through the group table. In addition, you want to calculate the average amount for each year.
Add a calculatdion field named SUM_AGG and input the formula SUM_AGG(contract amount)/SUM_AGG(purchase quantity) as follows:
Drag SUM_AGG field to Indicators.
Formula explanation:
Since the current analysis dimension is the Contract Signing Time (Year), the meaning of the formula is as follows:
Formula | Description |
SUM_AGG(contract amount) | The returned value is the summation of annual contract amount. |
SUM_AGG(purchase quantity) | The returned value is the summation of purchase quantity for each year. |
SUM_AGG(contract amount)/SUM_AGG(purchase quantity) | It is the average amount per year. For example, average amount in 2013 = 3887220/41. The total contract amount for the year 2013 is 3,887,220 and the purchase quantity is 41. |
Using Other Formula to Calculate the Average
Comparison of Formula Principles
Since the current analysis dimension is Contract Signing Time (Year), take the average amount of the contract in 2013 as an example. The formula meaning is shown as follows:
Formula | Calculation Order |
contract amount/purchase quantity | Calculate the average value of each contract in 2013 and then summarize and sum up the average value of all contracts. |
SUM_AGG(contract amount)/SUM_AGG(purchase quantity) | Summarize the contract amount and purchase quantity in 2013 and then divide the summation value of contracts in 2013 by the summation value of purchase quantity in 2013 to obtain the average value of contracts in 2013. |
Example
To help you better understand the SUM_AGG function, a comparison is made between not using it and using it in the same scenario.
Add a calculation field named Other Formula and directly use the formula contract amount/purchase quantity to calculate the average value.
Drag and drop Other Function field to Indicators and the result is shown as follows:
Obviously, the result obtained without using the SUM_AGG function is to divide the detailed data and then summarize and sum up it.
