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, StructuredParameter{,StructuredParameter})
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. |
| StructuredParameter |
One or more structured parameters by which the expression can be further expanded. A structured parameter is composed of a dimension and optionally of some sort criteria. The dimension is a single field and cannot be an expression. The dimension is used to determine the array of values the expression should apply to. The sort criteria can be:
In the above sorting types, REVERSE and DESCENDING mean the same. Regarding the sort type TEXT, ASCENDING and A2Z have the same meaning. DESCENDING, REVERSE and Z2A mean the same. Regarding the sort type LOAD_ORDER, ASCENDING and ORGINAL have the same meaning. |
| 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. This argument is optional. |
| 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. This argument is optional. |
|
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. This argument is optional. |
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).
Create a table with Customer, Product, UnitPrice, and UnitSales as dimensions. Add the expression to the table, as a measure.
| Example | Result |
|---|---|
| Avg(Aggr(Sum(UnitSales*UnitPrice), Customer)) |
The expression Aggr(Sum(UnitSales*UnitPrice), Customer) 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 '|');