Oracle® OLAP DML Reference 11g Release 2 (11.2) Part Number E17122-07 |
|
|
PDF · Mobi · ePub |
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.
DECIMAL when the data type of expression is DECIMAL or SHORT; otherwise, NUMBER.
MOVINGAVERAGE(expression, start, stop, step, -
[dimension [STATUS|limit-clause]])
A numeric variable or calculation whose values you want to average; for example, units
or sales-expense
.
Integer values that specify the range of values over which you want to average. The value of start specifies the beginning of the range. The value of stop specifies the end of the range. Specify the values of start and stop relative to the current value of dimension. To specify a negative position for start or stop preceed the value with a comma. Thus, you specify zero (0
) for the current dimension value, and -1
for the value preceding the current value. (See also "How the Moving Functions Handle Out-of-Range and NA Values".)
Note:
By default this function uses the default status list when identifying the range of values to average. You can specify either the STATUS or limit-clause phrase to change this behavior.Tip:
When you want to range to the end of status, for convience and to document your intent, specify the value of stop as OBJ function with the DIMMAX keywordA 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
and 3
, a step value of 2
means average over Mar96
, May96
, Jul96
, and Sep96
.
The dimension over which the moving average is calculated. The data type of dimension can be of any type, but typically, is a time dimension.
When expression has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want the function 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 uses the values specified by a LIMIT function or those specified with the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).
How the Moving Functions Handle Out-of-Range and NA Values
As a moving function loops through the values, at each step in the loop, if the in-loop dimension position is NA
or out of range, then the function considers expression as an NA
value. The function treats these NA
values in the manner specified by the setting of the NASKIP option (by default, as NA
) so, typically, the function ignores out-of-range (NA
) values and does not evaluate the expression for that step in the loop.
Example 8-31 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, Quarter
and 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 product
to 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 product
and 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