SUM_AGG- FineBI Help Document FineBI Document

Recent Updates: April 08, 2022

1. Overview

SUM_ AGG is used to summarize and sum the data of the specified dimension (dragged into the analysis column), and the calculation field will automatically adjust dynamically with the dimension as the user switches the analysis dimension.

Grammar	SUM_AGG(array)	According to the current analysis dimension, the summary value of the indicator field is returned. The generated result is a data column, and the number of rows is consistent with that of the current analysis dimension.
Parameter	array	The result that must be returned by a non-aggregate function formula can be the calculation result of an indicator field, dimension or indicator field and ordinary formula.

2. Precautions

Single value type parameters are supported in real-time data and can only be used when creating calculation fields in the dashboard.

3. Use aggregate functions average data

SUM_AGG is generally used for summation after detail filtering and division after summation. If you want to sum only one value, you can drag the field directly to the indicator to sum automatically.

If the user's horizontal axis dimension field is "day", the calculation field SUM_AGG(sales volume) of the vertical axis is the daily summary sales volume.

If the user's horizontal axis dimension field is "month", the calculation field SUM_AGG(sales volume) of the vertical axis is the total sales volume of each month.

For example, the user has obtained the monthly "PAID" and "AMOUNT" from 2008-01 to 2009-03 through the group table, and wants to obtain the monthly "average unit price", as shown in the figure below:

1) Add a new calculation field "Aggregate function averaging", and the operation method is shown in the following figure:

2) Drag "Aggregate function averaging" to the "indicator" bar, and the effect is shown in the following figure:

Formula Description:

Since the current analysis dimension is the SHIPDATE(Year Month), the meaning of the formula is as follows:

Formula	Description
SUM_AGG(PAID)	The return value is the summary value of the "PAID" amount for each month.
SUM_AGG(AMOUNT)	The return value is the summary value of "AMOUNT" quantity per month.
SUM_AGG(PAID)/SUM_AGG(AMOUNT)	Average unit price per month For example: average in 2008-01 = 572900 / 715900. In 2013, the "PAID" money is 572900, and the "AMOUNT" is 715900.

Formula

Description

SUM_AGG(PAID)

The return value is the summary value of the "PAID" amount for each month.

SUM_AGG(AMOUNT)

The return value is the summary value of "AMOUNT" quantity per month.

SUM_AGG(PAID)/SUM_AGG(AMOUNT)

Average unit price per month

For example: average in 2008-01 = 572900 / 715900.

In 2013, the "PAID" money is 572900, and the "AMOUNT" is 715900.

SUM_AGG counting is often nested with other functions (for example) for conditional summation. For details, see file: Conditional summation and counting.

4. Averaging using non aggregate functions

4.1 Comparison of formula principles

Since the current analysis dimension is the "SHIPDATE (Year Month)", taking the average unit price in 2008-01 as an example, the meaning of the formula is shown in the table below:

Formula	Calculation order
PAID/AMOUNT	First, calculate the "average value unit price" according to the formula PAID/AMOUNT of each "PAID" in 2013, and then "sum up" the average "AMOUNT" of all contracts in 2008-01
SUM_AGG(PAID)/SUM_AGG(AMOUNT)	First summarize the contract amount in 2013 and the purchase quantity in 2013, and then use the summary value of the contract amount in 2013 to divide the summary value of the purchase quantity in 2013 to obtain the average value of the contract in 2013 First summarize the "PAID" and "AMOUNT" in 2008-01, divide the summarized "PAID" by "AMOUNT", then we can get the average unit price of 2008-01.

Formula

Calculation order

PAID/AMOUNT

First, calculate the "average value unit price" according to the formula PAID/AMOUNT of each "PAID" in 2013, and then "sum up" the average "AMOUNT" of all contracts in 2008-01

SUM_AGG(PAID)/SUM_AGG(AMOUNT)

First summarize the contract amount in 2013 and the purchase quantity in 2013, and then use the summary value of the contract amount in 2013 to divide the summary value of the purchase quantity in 2013 to obtain the average value of the contract in 2013

First summarize the "PAID" and "AMOUNT" in 2008-01, divide the summarized "PAID" by "AMOUNT", then we can get the average unit price of 2008-01.

4.2 Example

In order to help users better understand aggregate functions, we do not use aggregate functions in the same scenario, which is compared with section 3 of this file.

1) Add a new calculation field "Non aggregate averaging", and directly use the formula PAID/AMOUNT, as shown in the following figure:

2) Drag "Non aggregate averaging" to the indicator bar, and the results are shown in the figure below:

Obviously, the result without aggregation function is to divide the detailed data and summarize it.

Already the First

Already the Last

Helpful
Not helpful
Only read

售前咨询电话

400-811-8890转1

在线技术支持

在线QQ：800049425

热线电话：400-811-8890转2

总裁办24H投诉

热线电话：173-1278-1526

文档反馈

鼠标选中内容，快速反馈问题

鼠标选中存在疑惑的内容，即可快速反馈问题，我们将会跟进处理。

不再提示

10s后关闭