Aggregate

Returns a number that is calculated by aggregating over the cells returned by the set expression. If a numeric expression is not provided, this function aggregates each metric within the current query context by using the default aggregation operator specified for each metric. If a numeric expression is provided, this function first evaluates, and then aggregates, the numeric expression for each cell in the specified set. The numeric expression must contain an existing AtScale metric or calculated metric that has been defined in Design Center (ad-hoc metrics are not supported).

Syntax

Aggregate(Set_Expression [ ,Numeric_Expression ])

Notes

Be aware of the following when using the Aggregate function:

• The Aggregate function in AtScale works with the aggregation functions listed below. Using other aggregation functions with Aggregate results in an error.

  • Sum: Returns the sum of values over the set.
  • Min: Returns the minimum value over the set.
  • Max: Returns the maximum value over the set.
  • Count: Returns the number of cells over the set.
  • Semi-Additive: Returns either the metric's additive or non-additive aggregated value, depending on the dimensions used in the query context.

• When Aggregate references a calculated metric, the function specified on the calculated metric itself is used for aggregation.

  When creating calculation groups, if you use Aggregate to reference a calculated metric, you must ensure that the calculated metric has an aggregation method set. If not, you may encounter errors at query time.

  For more information on setting aggregation functions for calculated metrics, see Add Calculated Metrics and the AtScale SML Object Documentation on GitHub.

Input Parameters

Set_Expression

An MDX expression that returns a set.

Numeric_Expression

An MDX expression that contains an existing AtScale matric or calculated metric that has been defined in Design Center (ad-hoc metrics are not supported).

Examples

This expression calculates the aggregate of all internet sales of th year previous to the current year to date:

Aggregate(
  PeriodsToDate(
      [Order Date Dimension].[Order Retail445].[Order Reporting_Year],
      ParallelPeriod(
          [Order Date Dimension].[Order Retail445].[Order Reporting_Year],
          1,
          [Order Date Dimension].[Order Retail445].CurrentMember
          )
      ),
      [Measures].[salesamount1]
  )