Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B1033902 


View PDF 
The MOVINGMIN function (abbreviated MVMIN) returns a series of minimum values for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGMIN searches the data for the minimum value in the range specified, relative to the current dimension value.
When the variable or expression has only the specified dimension, MOVINGMIN produces a single series of minimum values, one for each dimension value in the status. When the variable or expression has dimensions other than the one specified, MOVINGMIN produces a separate series of minimum values for each combination of values in the status list of the other dimensions.
Return Value
DECIMAL
Syntax
MOVINGMIN(expression, start, stop, step, [dimension [STATUSlimitclause]])
Arguments
A numeric variable or calculation from whose values you want to find the minimum 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 value preceding the current value. A comma is required before a negative start number.
Each minimum value is based on data for a specified range of dimension values preceding, including, or following the one for which the minimum value is being returned. To count the values in the range, MOVINGMIN 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, or every other value, or 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 minimum value that occurs in one of those four months.
The dimension over which the moving minimum 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 MOVINGMIN to use that dimension, you can omit the dimension argument.
Specifies that MOVINGMIN should use the current status list (that is, only the dimension values currently in status in their current status order) when calculating the moving minimum.
Specifies that MOVINGMIN should use the default status limited by limitclause when calculating the moving minimum. You can use any valid LIMIT clause (see the entry for the LIMIT command for further information). To specify that MOVINGMIN should use the current status limited by limitclause when calculating the moving minimum, specify a LIMIT function for limitclause.
Notes
MOVINGMIN is affected by the NASKIP option. When NASKIP is set to YES
(the default), MOVINGMIN ignores NA
values and returns the minimum value. Likewise, when some dimension values do not exist for a given range, MOVINGMIN returns the minimum value using whatever values do exist.
Suppose, for example, that Jan95
is the first month value in the workspace. When the current period is Feb95
and the range is 3
to 1
, Jan95
is the only month in the range 3
to 1
. The minimum value for Feb95
therefore uses only the Jan95
value.
When NASKIP is set to NO
, MOVINGMIN 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, MOVINGMIN returns NA
for either setting of NASKIP.
Examples
For an example of calculating minimum sales, see Example 189, "Calculating a Moving Average".