Aggr() returns an array of values for the expression calculated over the stated dimension or dimensions. For example, the maximum value of sales, per customer, per region. The Aggr function is used for advanced aggregations, in which the Aggr function is enclosed in another aggregation function, using the array of results from the Aggr function as input to the aggregation in which it is nested.
Syntax:
Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, dim{, dimension})
Return data type: dual
Arguments:
| Argument | Description |
|---|---|
| expr |
An expression consisting of an aggregation function. By default, the aggregation function will aggregate over the set of possible records defined by the selection. |
| dim | The dimension for which the array of values in the expression is determined. This is a single field and cannot be an expression. |
| dimension | Optional. One or more dimensions by which the expression can be further expanded. |
| SetExpression | By default, the aggregation function will aggregate over the set of possible records defined by the selection. An alternative set of records can be defined by a set analysis expression. |
| DISTINCT | If the expression argument is preceded by the distinct qualifier or if no qualifier is used at all, each distinct combination of dimension values will generate only one return value. This is the normal way aggregations are made – each distinct combination of dimension values will render one line in the chart. |
| NODISTINCT |
If the expression argument is preceded by the nodistinct qualifier, each combination of dimension values may generate more than one return value, depending on underlying data structure. If there is only one dimension, the aggr function will return an array with the same number of elements as there are rows in the source data. |
Basic aggregation functions, such as Sum, Min, and Avg, return a single numerical value while the Aggr() function can be compared to creating a temporary staged result set over which another aggregation can take place. For example, by computing an average sales value by summing the sales by customer in an Aggr() statement and then calculating the average of the summed results: Avg(TOTAL Aggr(Sum(Sales),Customer)).
Limitations:
Each dimension must be a single field, and cannot be an expression (calculated dimension).
| Customer | Product | UnitSales | UnitPrice |
|---|---|---|---|
| Astrida | AA | 4 | 16 |
| Astrida | AA | 10 | 15 |
| Astrida | BB | 9 | 9 |
| Betacab | BB | 5 | 10 |
| Betacab | CC | 2 | 20 |
| Betacab | DD | 25 | 25 |
| Canutility | AA | 8 | 15 |
| Canutility | CC | - | 19 |
Create a table with Customer, Product, UnitPrice, and UnitSales as dimensions.
| Example | Result |
|---|---|
| Avg(Aggr(Sum(UnitSales*UnitPrice), Customer)) |
Add the expression to the table, add as a measure. The values ofAggr(Sum(UnitSales*UnitPrice), Customer). This finds the total value of sales by Customer, and returns an array of values: 295, 715, and 120 for the three Customer values. These values are used as input to the Avg() function to find the average value of sales, 376.6667. (You must have Totals selected under Presentation in the properties panel. |
Data used in examples:
ProductData:
LOAD * inline [
Customer|Product|UnitSales|UnitPrice
Astrida|AA|4|16
Astrida|AA|10|15
Astrida|BB|9|9
Betacab|BB|5|10
Betacab|CC|2|20
Betacab|DD|25|25
Canutility|AA|8|15
Canutility|CC||19
] (delimiter is '|');