Oracle® OLAP DML Reference 11g Release 1 (11.1) B2812603 


PDF · Mobi · ePub 
The MOVINGMAX function (abbreviated MVMAX) returns a series of maximum values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGMAX searches the data for the maximum value in the range specified, relative to the current dimension value.
When the variable or expression has only the specified dimension, MOVINGMAX produces a single series of maximum values, one for each dimension value in the status. When the variable or expression has dimensions other than the one specified, MOVINGMAX produces a separate series of maximum values for each combination of values in the status list of the other dimensions
DECIMAL
MOVINGMAX(expression, start, stop, step, [dimension [STATUSlimitclause]])
A numeric variable or calculation from whose values you want to find the maximum values; for example, units
or salesexpense
.
A whole number that specifies the starting point of the range over which you want to search. The range is specified relative to the current value of dimension. Zero (0
) refers to the current value, and 1
refers to the period preceding the current value. A comma is required before a negative start number.
Each maximum value is based on data for a specified range of dimension values preceding, including, or following the one for which the maximum value is being returned. To count the values in the range, MOVINGMAX uses the default status, unless you use the STATUS keyword or the limitclause argument to specify a different dimension status.
A whole number that specifies the ending point of the range over which you want to search. A negative stop number must be preceded by a comma.
A positive whole number that specifies whether to search every value in the range, every other value, every third value, and so on. A value of 1
for step means search every value. A value of 2
means check 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 search the months Mar96
, May96
, Jul96
, and Sep96
and return the maximum value that occurs in one of those four months.
The dimension over which the moving maximum 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 MOVINGMAX to use that dimension, you can omit the dimension argument.
Specifies that MOVINGMAX should use the current status list (that is, only the dimension values currently in status in their current status order) when calculating the moving maximum.
Specifies that MOVINGMAX should use the default status limited by limitclause when calculating the moving maximum.
The syntax of limitclause is the same syntax as any of the limitclause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT commandafter the limittype argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT command (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).
To specify that MOVINGMAX should use the current status limited by limitclause , specify a LIMIT function for limitclause.
For an example of calculating maximum sales, see Example 831, "Calculating a Moving Average".