Aggr
Syntax:
Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter})
Return data type: dual
Arguments:
Argument | Description |
---|---|
|
An expression consisting of an aggregation function. By default, the aggregation function will aggregate over the set of possible records defined by the selection. |
|
The dimension is a single field and cannot be an expression. The dimension is used to determine the array of values the If sorting criteria are included, the array of values created by the For details of how to use sorting criteria, see Adding sorting criteria to the dimension in the structured parameter. |
|
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. |
|
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
Limitations:
Each dimension in an
Adding sorting criteria to the dimension in the structured parameter
In its basic form, the argument
In the
The argument
(FieldName, (Sort-type, Ordering))
Structured parameters can be nested:
(FieldName, (FieldName2, (Sort-type, Ordering)))
Sort-type can be:
The Ordering types associated with each Sort-type are as follows:
Sort type | Allowed Ordering types |
---|---|
|
|
|
|
|
|
|
|
The ordering types
For Sort-type
For Sort-type
Examples and results:
Example | Result |
---|---|
|
The expression Effectively, we have built a temporary list of values without having to create an explicit table or column containing those values. These values are used as input to the |
|
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. |
|
Using sorting criteria in the StructuredParameter argument in the expression: max(aggr(sum(Customers)-above(Sum(Customers)), (MonthYear,(NUMERIC, ASCENDING)))) Without sorting criteria the result of the expression max(aggr(sum(Customers)-above(Sum(Customers)), (MonthYear))) depends on how the dimension By adding values for sort type and ordering type to the dimension, we give sorting criteria to the structured parameter: (MonthYear, (NUMERIC, ASCENDING)), where the sort type Here, we are looking to find the greatest increase in number of customers, month-on-month. This could be used in a KPI visualization, for example. The Because we use sorting criteria with the dimension: |
Data used in examples:
Create a table with
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 '|');