|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
The MOVINGAVERAGE function (abbreviated MVAVG) computes a series of averages for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGAVERAGE computes the average of the data in the range specified, relative to the current dimension value.
When the data being averaged has only one dimension, MOVINGAVERAGE produces a single series of averages, one for each dimension value in status. When the data has dimensions other than the one being averaged over, MOVINGAVERAGE produces a separate series of averages for each combination of values in the status list of the other dimensions.
MOVINGAVERAGE(expression, start, stop, step, -
A numeric variable or calculation whose values you want to average; for example,
A whole number that specifies the starting point of the range over which you want to average. The range is specified relative to the current value of dimension. Zero (
0) refers to the current value, and
-1 refers to the value preceding the current value. A comma is required before a negative start number.
Each average is based on data for a specified range of dimension values preceding, including, or following the one for which the average is being calculated. To count the values in the range, MOVINGAVERAGE uses the default status, unless you use the STATUS keyword or the limit-clause argument to specify a different dimension status.
A whole number that specifies the ending point of the range over which you want to average. A negative stop number must be preceded by a comma.
A positive whole number that specifies whether to average over every value in the range, every other value, every third value, and so on. A value of
1 for step means average over every value. A value of
2 means average over the first value, the third value, the fifth value, and so on. For example, when the current month is
Jun96 and the start and stop values are
3, a step value of
2 means average over
The dimension over which the moving average is calculated. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR.
When expression has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want MOVINGAVERAGE to use that dimension, you can omit the dimension argument.
Specifies that MOVINGAVERAGE should use the current status list (that is, only the dimension values currently in status in their current status order) when calculating the moving average.
Specifies that MOVINGAVERAGE should use the default status limited by limit-clause when calculating the moving average. You can use any valid LIMIT clause (see the entry for the LIMIT command for further information). To specify that MOVINGAVERAGE should use the current status limited by limit-clause when calculating the moving average, specify a LIMIT function for limit-clause.
MOVINGAVERAGE is affected by the NASKIP option. When NASKIP is set to
YES (the default), MOVINGAVERAGE ignores
NA values and returns the average of the values that are not
NA. Likewise, when some dimension values do not exist for a given range, MOVINGAVERAGE returns the average using whatever values do exist.
Suppose, for example, that
Jan96 is the first month value in the workspace. When the current period being calculated is
Feb95 and the range is
Jan95 is the only month in the range
-1. The average for
Feb95 therefore uses only the
When NASKIP is set to
NO, MOVINGAVERAGE returns
NA when any value in the current range has a value of
NA, or when there are any dimension values that do not exist in the range.
When all data values for a calculation are
NA, or when no dimension values exist in the specified range, MOVINGAVERAGE returns
NA for either setting of NASKIP.
Example 18-9 Calculating a Moving Average
Suppose you have a variable called
sales that is dimensioned by a hierarchical dimension named
time, a dimension named
product, a dimension named
timelevelnames that contains the names of the levels of
time (for example,
Year), and a relation named
time.levelrels that relates the values of
time to the values of
timelevelnames. Assume also that using the following statements you limit
Womens - Trousers and time to quarters from
Q4-1999 to present.
LIMIT product TO 'Womens - Trousers' LIMIT timelevelnames TO 'Quarter' LIMIT time TO time.levelrels LIMIT time REMOVE 'Q1-1999' 'Q2-1999' 'Q3-1999'
After you have limited
sales, you issue the following report statement.
REPORT DOWN time sales - HEADING 'Running Yearly\nTotal' MOVINGTOTAL(sales, -4, 0, 1, time, - LEVELREL time.levelrels) - HEADING 'Minimum\nQuarter' MOVINGMIN(sales, -4, 0, 1, time, - LEVELREL time.levelrels) - HEADING 'Maximum\nQuarter' MOVINGMAX(sales, -4, 0, 1, time, - LEVELREL time.levelrels) - HEADING 'Average\nQuarter' MOVINGAVERAGE(sales, -4, 0, 1, time, - LEVELREL time.levelrels)
The following report was created by the preceding statement.
-----------------------PRODUCT------------------------ ------------------Womens - Trousers------------------- Running Yearly Minimum Maximum Average TIME SALES Total Quarter Quarter Quarter -------------- ---------- ---------- ---------- ---------- ---------- Q4-1999 416 1,386 233 480 346.50 Q1-2000 465 1,851 233 480 370.20 Q2-2000 351 1,969 257 480 393.80 Q3-2000 403 2,115 351 480 423.00 Q4-2000 281 1,916 281 465 383.20 Q1-2001 419 1,919 281 465 383.80 Q2-2001 349 1,803 281 419 360.60 Q3-2001 467 1,919 281 467 383.80 Q4-2001 484 2,000 281 484 400.00 Q1-2002 362 2,081 349 484 416.20 Q2-2002 237 1,899 237 484 379.80 Q3-2002 497 2,047 237 497 409.40 Q4-2002 390 1,970 237 497 394.00