Aggr - chart function

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:  

Basic aggregation functions, such as Sum, Min, and Avg, return a single numerical value, whereas the Aggr() function can be compared to creating a temporary staged result set (a virtual table), over which another aggregation can be made. 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 in an Aggr() function must be a single field, and cannot be an expression (calculated dimension).

Adding sorting criteria to the dimension in the structured parameter

In its basic form, the argument StructuredParameter in the Aggr function syntax is a single dimension. The expression: Aggr(Sum(Sales, Month)) finds the total value of sales for each month. However, when enclosed in another aggregation function, there can be unexpected results unless sorting criteria are used. This is because some dimensions can be sorted numerically or alphabetically, and so on.

In the StructuredParameter argument in the Aggr function, you can specify sorting criteria on the dimension in your expression. This way, you impose a sort order on the virtual table that is produced by the Aggr function.

The argument StructuredParameter has the following syntax:

(FieldName, (Sort-type, Ordering))

Structured parameters can be nested:

(FieldName, (FieldName2, (Sort-type, Ordering)))

Sort-type can be: NUMERIC, TEXT, FREQUENCY, or LOAD_ORDER.

The Ordering types associated with each Sort-type are as follows:

The ordering types REVERSE and DESCENDING are equivalent.

For Sort-type TEXT, the ordering types ASCENDING and A2Z are equivalent, and DESCENDING, REVERSE, and Z2A are equivalent.

For Sort-type LOAD_ORDER, the ordering types ASCENDING and ORIGINAL are equivalent.

Examples and results:  

Data used in examples:

Create a table with Customer, Product, UnitPrice, and UnitSales as dimensions. Add the expression to the table, as a measure.

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 '|');