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({[DISTINCT] [NODISTINCT ]} expr, dim{, dimension})

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.

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 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)).

Tip: Use this function in calculated dimensions if you want to create nested chart aggregation in multiple levels.

Limitations:  

Each dimension must be a single field, and cannot be an expression (calculated dimension).

Examples and results:

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 UnitSales as dimensions.

Example Result
Avg(Aggr(Sum(UnitSales*UnitPrice), Customer))

Add the expression to the table, add as a measure. The values of Aggr(Sum(UnitSales*UnitPrice), Customer). This 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 '|');