Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

MOVINGMAX

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

Return Value

DECIMAL when the data type of expression is DECIMAL or SHORT; otherwise, NUMBER.

Syntax

MOVINGMAX(expressionstartstopstep, [dimension [STATUS|limit-clause]])

Parameters

expression

A numeric variable or calculation from whose values you want to find the maximum values; for example, units or sales-expense.

start
stop

Integer values that specify the range of values over which you want to find the maximum values. 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 keyword
step

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.

dimension

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.

STATUS

Specifies that MOVINGMAX 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).

Examples

For an example of calculating maximum sales, see Example 8-31, "Calculating a Moving Average".