Skip Headers
Oracle® OLAP Expression Syntax Reference
Release 11.2

E23381-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
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

MIN

MIN returns the smallest of a selection of data values calculated over a Time dimension.

The GREGORIAN relations superimpose the Gregorian calendar on the Time dimension. These relations can be useful for calculations on fiscal and nonstandard hierarchies.

Return Value

NUMBER

Syntax

MIN (value_expr) OVER (window_clause)

window_clause::=

[ { DIMENSION dimension_id | HIERARCHY hierarchy_id } ]
    BETWEEN preceding_boundary | following_boundary 
     [WITHIN { LEVEL
              | PARENT
              | GREGORIAN {YEAR | QUARTER | MONTH | WEEK}
              | ANCESTOR AT { DIMENSION LEVEL dim_level_id
                             | HIERARCHY LEVEL hier_level_id 
                             }
              }
     ]

preceding_boundary::=

{UNBOUNDED PRECEDING | expression PRECEDING} AND 
   { CURRENT MEMBER 
   | expression {PRECEDING | FOLLOWING}
   | UNBOUNDED FOLLOWING
   }

following_boundary::=

{CURRENT MEMBER | expression FOLLOWING} AND
  { expression FOLLOWING 
  | UNBOUNDED FOLLOWING
  }

Arguments

value_expr

A dimensional expression whose values you want to calculate.

dimension_id

The Time dimension over which the values are calculated using the default hierarchy.

hierarchy_id

The hierarchy over which the values are calculated. If dimension_id is used instead, the default hierarchy is used.

dim_level_id

The name of a level of dimension_id.

hier_level_id

The name of a level of hierarchy_id.

boundaries

The preceding_boundary and following_boundary identify a range of time periods within the group identified by the dimension or hierarchy.

UNBOUNDED starts with the first period or ends with the last period of the group.

CURRENT MEMBER starts or ends the calculation at the current time period.

expression

A numeric value identifying a period at a particular distance from the current time period that starts or ends the range.

WITHIN subclause

Identifies the range of time periods used in the calculation. Following are descriptions of the keywords.

Range Description
LEVEL Calculates all time periods at the same level. (Default)
PARENT Calculates time periods at the same level with the same parent.
GREGORIAN YEAR Calculates time periods within the same Gregorian year.
GREGORIAN QUARTER Calculates time periods within the same Gregorian quarter.
GREGORIAN MONTH Calculates time periods within the same Gregorian month.
GREGORIAN WEEK Calculates time periods within the same Gregorian week.
ANCESTOR Includes time periods at the same level and with the same ancestor at a specified level.

Example

This example calculates the minimum value between the current member and all subsequent members in the same calendar year. The selection of the data displays the minimum values for the months in 2006.

MIN(GLOBAL.UNITS_CUBE.UNITS) OVER (DIMENSION GLOBAL.TIME BETWEEN CURRENT MEMBER AND UNBOUNDED FOLLOWING WITHIN ANCESTOR AT DIMENSION LEVEL GLOBAL.TIME.CALENDAR_YEAR)

Time Units Minimum
JAN-06 47776 45419
FEB-06 47695 45419
MAR-06 51348 45419
APR-06 47005 45419
MAY-06 52809 45419
JUN-06 45419 45419
JUL-06 48388 46354
AUG-06 48830 46354
SEP-06 46354 46354
OCT-06 47411 46842
NOV-06 46842 46842
DEC-06 55052 55052

Related Topics

AVG, COUNT, MAX, SUM