Aggr() returns an array of values of the expression calculated over dimensions. The Aggr function is used for advanced aggregations.
Basic aggregation functions, such as Sum, Min, and Avg, return a single numerical value while the result of an advanced aggregation can be compared to a temporary straight table that can be used in charts. To obtain a final aggregation from this temporary table, the Aggr function should be placed inside a basic aggregation function, for example,Sum, Max or Count.
Syntax:
Aggr({[DISTINCT] [NODISTINCT ]} expr, dim{, Expression})
Return data type: dual
Arguments:
| Argument | Description |
|---|---|
| expr |
By default, the aggregation function will aggregate over the set of possible records defined by the selection. |
| dim | Single field. Cannot be an expression. |
| Expression | Optional expressions or fields containing the range of data to be measured. |
| 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. |
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 UntiSales as dimensions.
| Example | Result |
|---|---|
| Aggr(Max(UnitPrice), Customer) |
An array of values: 16, 20, 15, and 25. The expression finds the maximum UnitPrice by Customer. 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. |
| Min(Aggr(Max(UnitPrice), Customer)) |
15. The expression finds the maximum UnitPrice by Customer, and finds the minimum value of the result. By using the Aggr expression as input to the Min() function, the minimum value of the array produced by the Aggr() is found. Effectively, by enclose the Aggr() function in another aggregation we have built a temporary list of values without having to create a separate chart containing those values. |
| Aggr(NODISTINCT Max(UnitPrice), Customer) |
An array of values: 16, 16, 16, 25, 25, 25, 15, 15, 25, and 25. 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:
Temp:
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 1 25 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!