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({[DISTINCT] [NODISTINCT ]} expr, dim{, dimension})
Return data type: dual
Arguments:
| Argument | Description |
|---|---|
| expr |
An expression usually 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. |
| 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 straight table that can be used in a chart. For example, finding the maximum value by customer. We can then find the minimum value of the resulting temporary table. You use the Aggr() function to nest the initial aggregation and place that inside a basic aggregation function, for example,Sum, Max or Count. For example: Min(Aggr(Max(Value),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 |
| Canutility | AA | 8 | 15 |
| Canutility | CC | - | 19 |
Create a table with Customer, Product, UnitPrice, and UntiSales as dimensions.
| Example | Result |
|---|---|
| Min(Aggr(Max(UnitPrice), Customer)) |
The part of the expression Aggr(Max(UnitPrice), Customer)finds the maximum UnitPrice by Customer, and returns an array of values: 16, 19, and 25. These can be seen in the table rows in the measure column. The aggregation Max(UnitPrice)produces a result for each Product by Customer. By using this expression as the expr argument in the Aggr() function and Customer as the dim argument, we can find the result of Max(UnitPrice) by Customer. Effectively, we have built a temporary list of values without having to create a separate chart containing those values. The totals row for the measure returns 15 as a result of the Aggr() function enclosed in the Min() aggregation. Ig is the minimum value f the array returned by the Aggr() expression. |
| Aggr(NODISTINCT Max(UnitPrice), Customer) |
An array of values: 16, 16, 16, 25, 25, 25, 19, and 19. The nodistinct qualifier means that the array contains one element for each row in the source data: each is the maximum UnitPrice for each Customer and Product. |
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
Canutility|AA|8|15
Canutility|CC||19
] (delimiter is '|');
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!