AVG

AVG returns the average of a selection of values calculated over a Time dimension.

Return Value

NUMBER

Syntax

AVG (value_expr) OVER HIERARCHY 
    ( [ dimension | hierarchy ]
	   BETWEEN start AND end [WITHIN relation]
    )

Arguments

value_expr is a dimensional expression whose values you want to calculate.

dimension is the Time dimension over which the values are calculated.

hierarchy is theTime hierarchy over which the values are calculated. Otherwise, the default Time hierarchy is used.

start and end are the boundaries of the calculation, which are expressed with one of these clauses:

• [UNBOUNDED | number]{ PRECEDING | FOLLOWING } identifies a range of dimension members within the group defined by relation for the calculation.
  • UNBOUNDED starts with the first member or ends with the last member of the group.
  • number identifies a dimension member at a particular distance from the current member.
• CURRENT MEMBER starts or ends the calculation at the current member.

relation can be any of the following. The GREGORIAN relations superimpose the Gregorian calendar on the Time dimension. These relations can be useful for calculations on fiscal and non-standard hierarchies.

• PARENT includes members at the same level with the same parent in the calculation.

• LEVEL includes all members at the same level in the calculation.

• ANCESTOR AT LEVEL level includes members at the same level and with the same ancestor at a specified level.

• GREGORIAN YEAR calculates Time periods within the same Gregorian year.

• GREGORIAN QUARTER calculates Time periods within the same Gregorian quarter.

• GREGORIAN MONTH calculates Time periods within the same Gregorian month.

• GREGORIAN WEEK calculates Time periods within the same Gregorian week.

Example

The following example calculates a running average for quarters within each calendar year.

AVG(GLOBAL.UNITS_CUBE.UNITS) OVER HIERARCHY (GLOBAL.TIME.CALENDAR BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN PARENT)

Related Topics

COUNT, MAX, MIN, SUM

Copyright © 2003, 2007, Oracle. All rights reserved.