Overview
Application Scenario
Generally, to achieve an aggregation calculation, you need to use aggregate functions to summarize the values of a set of data and then use the summarized values to perform a further calculation.
What is the total sales of the company (Sum: SUM_AGG(Sales))?
What is the average price of the product (Average: AVG_AGG(Price))?
How many members in total (Distinct Count: COUNTD_AGG(Members))?
What is the maximum order amount of yesterday's orders (Maximum: MAX_AGG(Order Amount))?
As you switch analysis dimensions, calculation fields automatically adjust to the changed dimensions. For example, if the dimension dragged into the field is Month, then the calculation is:
What is the total sales for each month of the company?
What is the average price for each month of the product?
You can also obtain these summary values through the summary modes for tables, but using functions allows you to further calculate these summarized values: when you want to continue writing the total value into a formula, you can only use aggregate functions.
Function Description
Different aggregate functions correspond to different summary modes, including sum, average, median, maximum, minimum, standard deviation, variance, distinct count, count.
Function Entry
Aggregate functions are generally used for flexible analysis in components, as shown in the following figure.
Note:
Data List
Type | Function | Usage | Example |
Scale | Calculating the sum value. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field SUM_AGG(sales) is the total sales for each day. 2. 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. | |
Calculate the average. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field AVG_AGG(sales) is the average sales for each day. 2. If the dimension field in the lateral axis is Month, the value returned by the calculation field AVG_AGG(sales) is the average sales for each month. | ||
Calculating the count. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field COUNT_AGG(sales) is the count of sales for each day. 2. If the dimension field in the lateral axis is Month, the value returned by the calculation field COUNT_AGG(sales) is the count of sales for each month. | ||
Calculating the distinct count. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field COUNTD_AGG(sales) is the distinct count of sales for each day. 2. If the dimension field in the lateral axis is Month, the value returned by the calculation field COUNTD_AGG(sales) is the distinct count of sales for each month. | ||
Describing the whole from a part. | Returns the minimum value in the parameter list. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field MIN_AGG(sales) is the minimum sales for each day. 2. If the dimension field in the lateral axis is Month, the value returned by the calculation field MIN_AGG(sales) is the minimum sales for each month. | |
Returns the maximum value in the parameter list. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field MAX_AGG(sales) is the maximum sales for each day. 2. If the dimension field in the lateral axis is Month, the value returned by the calculation field MAX_AGG(sales) is the maximum sales for each month. | ||
Calculating the median value. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field MEDIAN_AGG(sales) is the median sales for each day. 2. If the dimension field in the lateral axis is Month, the value returned by the calculation field MEDIAN_AGG(sales) is the median sales for each month. | ||
Calculating the discreteness. | Calculating the variance. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field VAR_AGG(sales) is the variance of sales for each day. 2. If the dimension field in the lateral axis is Month, the value returned by the calculation field VAR_AGG(sales) is the variance of sales for each month. | |
Calculating the standard deviation. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field STDEV_AGG(sales) is the standard deviation of sales for each day. 2. If the dimension field in the lateral axis is Month, the value returned by the calculation field STDEV_AGG(sales) is the standard deviation of sales for each month. | ||
Calculating the percentage. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field PERCENTILE_AGG(sales,0.95) is 95% of the sales for each day. 2. If the dimension field in the lateral axis is Month, the value returned by the calculation field PERCENTILE_AGG(sales,0.95) is 95% of the sales for each month. | ||
APPROX_COUNTD_AGG | Calculating the approximate distinct count. | 1. If the dimension field in the lateral axis is Day, the value returned by the calculation field APPROX_COUNTD_AGG() is the approximate distinct count of sales for each day. 2. If the dimension field in the lateral axis is Month, the value returned by the calculation field APPROX_COUNTD_AGG() is the approximate distinct count of sales for each month. |
Previous：Comparison Between BI Function and Excel Function
