Aggregate(x, map, I, J)

Aggregates the values in array «x» over index «I» to produce a result indexed by «J», a coarser grained value. For example, «I» and «J» might be days and months, or countries and continents, respectivly. Parameter «map» is indexed by «I». It gives the value of «J» for each element of «I». It gives a warning if any value in «map» is not in index «J», unless you set «noMapError» to True.

For example, suppose we want to aggregate Population_by_country indexed by Country to produce an array of populations by Continent. We need a map Continent_by_country giving the name of the continent containing each (indexed by Country):

Aggregate(Population_by_country, Continent_by_country, Country, Continent)

returns the population by continent.

Here's an example for how to aggregate from months to years:

Index Months := Sequence(MakeDate(2009, 1, 1), MakeDate(2019, 12, 1), dateUnit: "M")
Index Years := 2009..2020
Year_by_months := DatePart(Months, "Y")
Revenue_by_month := { an array indexed by Months }
Revenue_by_year := Aggregate(Revenue_by_month, Year_by_months, Months, Years)

Optional Parameters


By default, Aggregate sums the values. But you can set another method for Type parameter, including Average, Max, Min, Median, First, Last, Count, or Sum. In fact, you can use any array-reducing function -- i.e. a function that reduces the number of dimensions over an index. You can even use a user-defined function that is an array-reducing function. The «type» parameter may be a text value -- e.g. "Product" -- or a handle to the function -- e.g. Handle(Product).


False by default. If True, «map» must contain the integer positions of the corresponding element of «targetIndex» rather than their values.


The value in the result array when no value in «x» maps to that value of «targetIndex». No warning is issued when there are result cells with no data. The default «defaultValue» is Null. If you'd like to use a different value, such as 0, you can specify the value like so:

Aggregate(x, map, i, targetIndex, type: "SDeviation", defaultValue: 0)

Aggregate ignores Null values in «x».


(new to Analytica 5.0)

When the value in «map» is not in the target index «J» an error is normally issued (except for «map»=Null values), which is often useful for debugging your expression. But, if your target index «J» is intentionally a subset of the full set of values in «map», you can set «noMapError» to true to suppress the error and ignore the values in «x» that don't map into «J».


The «noMapError» parameter is useful when tallying values for the US states only in this example.

Aggregate noMapError.png
Aggregate( Annual_usage, City_to_province, City, US_State, noMapError:true )

Aggregation type

By default, Aggregate sums over specified elements. Use the optional «type» parameter to specify another aggregation method, such as Sum (default), 'Count', Max, Min, Average or Mean, Product, Median, SDeviation, Variance, and JoinText -- or any array-reducing function. You can specify the identifier of the function as a text value, or a handle to the function. It could even be an array of functions.

Revenue_dev := Aggregate(Revenue_by_month, MonthToYear, Month, Year, Type: "SDeviation")
Peak_revenue := Aggregate(Revenue_by_month, MonthToYear, Month, Year, Type: Handle(Max))
Ave_revenue := Aggregate(Revenue_by_month, MonthToYear, Month, Year, Type: "Average")

You can also create your own custom aggregation method in the form of a User-Defined Function. The function should accept an array parameter and an index, and should reduce the array so that the result eliminates the indicated index, for example:

Function First(A: Array[I]; I: Index) := A[@I = 1]
Opening_Revenue := Aggregate(Revenue_by_month, MonthToYear, Month, Year, Type: "First")
Function Last(A: Array[I]; I: Index) := A[@I = Size(I)]
Closing_Revenue := Aggregate(Revenue_by_month, MonthToYear, Month, Year, Type: "Last")

(Note that 'First' and 'Last', as text, are already built-in options in Analytica 5.0).

Function PosMax(A: Array[I]; I: Index) := ArgMax(A, I, position: true)
Best_Month := Aggregate(Revenue_by_month, MonthToYear, Month, Year, Type: Handle(PosMax))

To return a "Count", you simply need to use a Boolean expression for the first parameter. No need to specify the «type» parameter, which defaults to 'Sum'. For example, to count the number of months in each year with positive revenue:

Aggregate(Revenue_by_month > 0, MonthToYear, Month, Year)

in Analytica 5.0 and later, "Count" is accepted as a «type», which counts the number of non-null entries (zeros are counted).

Note: Aggregation (aka conglomeration) functions can also be utilized by the MdTable function. The requirements are the same.

Positional Maps

The «map» parameters to specifies how each element in the original index «I» maps into the «J». This is usually a many-to-one mapping. Your «map» is necessarily indexed by «I», and each element of «map» specifies an element of «J». When specifying the target values, you can do so either by value or by position. The default assumes that map contains values from «targetIndex», but by specifying the optional «positional» parameter as true, «map» is interpreted as containing positions.

Variable M_to_Y_pos := Floor((@Month-1)/12) + 1
Variable Annual_Rev := Aggregate(Revenue_by_month, M_to_Y_pos, Month, Year, positional: true)

The «map» may contain Null values. With a positional mapping, any «I»-position in which «map» is Null does not get included in the aggregated result. With a value-based mapping, these elements are included only if one of the elements of «targetIndex» is «null» (which is uncommon).

Inverses of Aggregate (De-aggregation)

Since Aggregate is used to map data from a finer-grained index to a coarser-grained index, it is natural to ask how the inverse is accomplished -- namely, mapping from a coarse-grained index to a fine-grain index.

Just as with aggregate, there are many potential types of de-aggregation. For example, when de-aggregating annual data down to months, we might use the annual value for every month in that year, or split it uniformly among all the target months, or perhaps assign it to only one month, using zero for all other months in that year.

The following uses the annual value for every month in that year. In the example, Annual_val is indexed byYear, while the result is indexed by Month:

Annual_val[Year = MonthToYear]

To spread the value uniformly across all months, we can do this:

Var MonthsPerYear := Aggregate(1, MonthToYear, Month, Year);
(Annual_val/MonthsPerYear)[Year = MonthToYear]

More generally, if we have a weighting, w, indexed by Month, which specifies the proportion of annual revenue each month receives, we can use:

w*Annual_val[Year = MonthsToYear]

If you need to ensure that w is normalized for each month, use as the weight:

w := (w0/Aggregate(1, MonthToYear, Month, Year)[Year = MonthToYear])

where w0 is the unnormalized weight. Using w0 := 1 yields the earlier example of spreading evenly across all months for the year.

Using an array w that has a 1 in the first month of each year, and 0 for all remaining months, yields a de-aggregation that assigns the full annual value to the first month of each year:

Index Firsts := Unique(MonthToYear, Month);
(@[Firsts = Month] > 0)*Annual_val[Year = MonthToYear]

Other methods for Aggregation

In many existing Analytica models, one will see the following method used for aggregation:

Sum((MonthToYear = Year) * Revenue, Month)

This expression is functionally equivalent to:

Aggregate(Revenue, MonthToYear, Month, Year)

However, the use of Aggregate will evaluate much faster. When using the dot-product method with Sum, the intermediate expression (MonthToYear = Year) expands to a full 2-D array (Month x Year), which even though it is very sparse, still consumes lots of memory and time to process. In rough terms, the Sum-based method is O(n2) complexity, while Aggregate is only O(n).

The Frequency function can be used to aggregate, with either "sum" type aggregation (using the data itself as the weighting parameter) or "count" type aggregation. Frequency is in fact quite efficient for this purpose, but the usage for the purpose is unintuitive, making Aggregate the preferred alternative.

Slice Assignment can be used in a procedural-programming style to aggregate. This is a very general-purpose approach (can be employed for a variety of aggregation types) and can also be quite efficient. The general approach is demonstrated here (for Max-type aggregation):

Var res := Null;
For m := Month Do (
res[Year = MonthToYear] := Max([res[Year = MonthToYear], Revenue[Month = m]])


See Also


You are not allowed to post comments.