# AVG

`AVG` returns the average of a selection of values calculated over a Time dimension. Use this function to create cumulative averages and moving averages.

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

```AVG (value_expr) OVER (window_clause)
```

window_clause::=

```[ { DIMENSION dimension_id | HIERARCHY hierarchy_id } ]
BETWEEN preceding_boundary | following_boundary
[WITHIN { LEVEL
| PARENT
| 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.

Range Description
`LEVEL` Calculates all time periods at the same level. (Default)
`PARENT` Calculates time periods at the same level with the same parent.
`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.
`ANCESTOR` Includes time periods at the same level and with the same ancestor at a specified level.

Example

This example calculates a cumulative average within each parent. The selection of data shows the cumulative averages for quarters within the 2005 and 2006 calendar years.

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

TIME PARENT UNITS AVERAGE
Q1.05 CY2005 143607 143607
Q2.05 CY2005 138096 140852
Q3.05 CY2005 138953 140219
Q4.05 CY2005 145062 141430
Q1.06 CY2006 146819 146819
Q2.06 CY2006 145233 146026
Q3.06 CY2006 143572 145208
Q4.06 CY2006 149305 146232

Related Topics