SUM

SUM returns the total of a selection of values calculated over a Time dimension. You can use the SUM function to create period-to-date calculations.

The GREGORIAN relations superimpose the Gregorian calendar on the Time dimension. These relations can be useful for calculations on fiscal and nonstandard hierarchies.

Return Value

NUMBER

Syntax

SUM (value_expr) OVER (window_clause)

window_clause::=

[ { DIMENSION dimension_id | HIERARCHY hierarchy_id } ]
    BETWEEN preceding_boundary | following_boundary 
      [WITHIN { PARENT
               | LEVEL
               | GREGORIAN {YEAR | QUARTER | MONTH | WEEK}
               | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                              | HIERARCHY LEVEL hier_level_id 
                              }
               }
      ]

preceding_boundary::=

{UNBOUNDED PRECEDING | expression PRECEDING} AND 
   { CURRENT MEMBER 
   | expression {PRECEDING | FOLLOWING}
   | UNBOUNDED FOLLOWING
   }

following_boundary::=

{CURRENT MEMBER | expression FOLLOWING} AND
  { expression FOLLOWING 
  | UNBOUNDED FOLLOWING
  }

Arguments

value_expr

A dimensional expression whose values you want to calculate.

dimension_id

The Time dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

boundaries

The preceding_boundary and following_boundary identify a range of time periods within the group identified by the dimension or hierarchy.

UNBOUNDED starts with the first period or ends with the last period of the group.

CURRENT MEMBER starts or ends the calculation at the current time period.

expression

A numeric value identifying a period at a particular distance from the current time period that starts or ends the range.

WITHIN subclause

Identifies the range of time periods used in the calculation. Following are descriptions of the keywords.

Example

This example calculates the sum of two values, for the current and the following time periods, within a level. The results appear in the Sum column.

SUM(UNITS_CUBE.SALES) OVER (DIMENSION "TIME" BETWEEN 1 PRECEDING AND CURRENT MEMBER WITHIN LEVEL)

The next example calculates Year-to-Date Sales.

SUM(UNITS_CUBE.SALES) OVER (HIERARCHY TIME.CALENDAR BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)

Related Topics

AVG, COUNT, MAX, MIN