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 measure within the current query context by using the default aggregation operator specified for each measure. If a numeric expression is provided, this function first evaluates, and then sums, the numeric expression for each cell in the specified set. The numeric expression must contain an existing AtScale measure or calculated measure that has been defined in Design Center (ad-hoc measures 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.

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

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

  For more information on setting aggregation functions for calculated measures, see Add Calculated Measures.

Input Parameters

Set_Expression
An MDX expression that returns a set.

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

Examples

This expression calculates the aggregate of all internet sales of the 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]
  )