| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
The MOVINGTOTAL function (abbreviated MVTOT) computes a series of totals for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGTOTAL computes the total of the data in the range specified, relative to the current dimension value.
When the variable or expression has only the specified dimension, MOVINGTOTAL produces a single series of totals, one for each dimension value in the status. When the variable or expression has dimensions other than the one specified, MOVINGTOTAL produces a separate series of totals for each combination of values in the status list of the other dimensions.
Return Value
DECIMAL
Syntax
MOVINGTOTAL(expression, start, stop, step, [dimension [STATUS|limit-clause]])
Arguments
A numeric variable or calculation whose values you want to total; for example, UNITS or SALES-EXPENSE.
A whole number that specifies the starting point of the range over which you want to total. The range is specified relative to the current value. 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 total is based on data for a specified range of dimension values preceding, including, or following the one for which the total is being calculated. To count the values in the range, MOVINGTOTAL 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 total. A negative stop number must be preceded by a comma.
A positive whole number that specifies whether to total over every value in the range, every other value, every third value, and so on. A value of 1 for step means total over every value. A value of 2 means total over the first value, the third value, the fifth value, and so on. When the current month is Jun96 and the start and stop values are -3 and 3, a step value of 2 means total over Mar96, May96, Jul96, and Sep96.
The dimension over which the moving total is calculated. While this can be any dimension, it is typically a time dimension.
When expression has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want MOVINGTOTAL to use that dimension, you can omit the dimension argument.
Specifies that MOVINGTOTAL should use the current status list (that is, only the dimension values currently in status in their current status order) when calculating the moving total.
Specifies that MOVINGTOTAL should use the default status limited by limit-clause when calculating the moving total. You can use any valid LIMIT clause (see the entry for the LIMIT command for further information). To specify that MOVINGTOTAL should use the current status limited by limit-clause when calculating the moving total, specify a LIMIT function for limit-clause.
Examples
For an example of calculating a moving total sales, see Example 20-9, "Calculating a Moving Average".
