Skip to main content
Version: I2023.2.1

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

note

Note: The AGGREGATE function does not support all measure aggregation functions. For example, "Distinct Count" and "Average" are not supported. Using the AGGREGATE function with an unsupported Measure function will result in the following error message, "Calculation contains Aggregate over an unsupported automatic reaggregation type". In many cases, this issue can be avoided by using a supported in-line aggregation function, such as AVG, rather than the AGGREGATE function. (ATSCALE-13129)

Syntax​

Aggregate(Set_Expression [ ,Numeric_Expression ])

Notes​

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.

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