Numeric Functions

SUM(<set>[, <numeric_value_expression>])

Sums set based on an optional numeric value expression.

For example,

SUM({USA, FRANCE}, Sales.VALUE)

will return the sum of sales for USA and France. This can also be written as

SUM({USA, FRANCE}, Sales)

which is a more intuitive way. The production <numeric_value_expression> ::= <tuple>[.VALUE] is used, and the optional VALUE keyword is left out.

If a numeric value expression is not specified, then it is implied by the other coordinates that appear in the MDX statement. For example, in the statement

WITH MEMBER Geography.NewMember AS SUM({USA, France})
SELECT NewMember ON COLUMNS,
         Quarters.MEMBERS ON ROWS
   FROM SalesCube
WHERE ([1991], [Salesperson].[All], Sales, [Products].[All])

the ROWS expression provides a coordinate from the Quarters dimension. The WHERE clause provides the other four coordinates from the Years, Products, Salesperson, and Measures dimensions. These coordinates together identify a value in the cube for USA. They also identify a value for France. The SUM function adds these two values.

AGGREGATE(<set>[, <numeric_value_expression>])

This function uses the “proper” aggregate function based on the context. For example, consider the following expression:

WITH MEMBER Geography.Total AS AGGREGATE({USA, France})
SELECT {Measures.SumSales, Measures.MaxSales} ON COLUMNS,
      {USA, France, Total} ON ROWS
FROM SalesCube
WHERE ([1991], [Products].[All], [Salesperson].[All], 
      [Quarters].[All])

In this expression, the calculated member Total is displayed against the measure “SumSales” and the measure “MaxSales.” In the former case, the provider computes Total by adding (with SUM) and in the latter case, the provider computes Total by taking the maximum. What happens when the set argument contains members from the MEASURES dimension is provider-specific.

COUNT(<set>[, INCLUDEEMPTY])

Counts the number of tuples in set. The optional INCLUDEEMPTY flag includes empty cells in the count. For more information, see “Empty Cells."

AVG(<set>[, <numeric_value_expression>])

Computes the average of the tuples in set based on numeric_value_expression. Similar numeric functions are:

• MEDIAN(<set>[, <numeric_value_expression>])
  
  
• MIN(<set>[, <numeric_value_expression>])
  
  
• MAX(<set>[, <numeric_value_expression>])
  
  
• VARIANCE(<set>[, <numeric_value_expression>])
  
  
• STDDEV(<set>[, <numeric_value_expression>])
  
  
• RANK(<tuple>, <set>)

Note   Each of these functions requires an implicit count of the number of cells, which does not include empty cells. In order to force the inclusion of empty cells, the application must use the COALESCEEMPTY() function. For more information, see “Empty Cells” in this chapter.