InterSystems MDX includes extensions for use with time levels. These include the NOW member and the %TIMERANGE function.
Selecting a Member Relative to Today (Time Levels)
For date/time levels, the system supports a special member called NOW, which uses the current date (runtime) and accesses the appropriate member of the level.
For example, the following query accesses the current year in the Year dimension:
SELECT birthd.year.NOW ON 1 FROM demomdx
2011 9
For another example:
SELECT birthd.[quarter year].NOW ON 1 FROM demomdx
Q2 2011 5
Business Intelligence also supports variations that indicate members that are offset from NOW. For example, [NOW-1] finds the member that precedes NOW by one position:
SELECT birthd.[quarter year].[NOW-1] ON 1 FROM demomdx
Q1 2011 1
You can use these variations within ranges of members like the following:
SELECT birthd.[quarter year].[now-1]:birthd.[quarter year].now ON 1 FROM demomdx
1 Q1 2011 1
2 Q2 2011 5
For more details, see NOW Member for Date/Time Levels in the InterSystems MDX Reference.
Selecting Ranges of Members of a Time Level
The system provides an extension to MDX that enables you to define a range of members, for a time level. This extension is the %TIMERANGE function, which takes three arguments: a starting member, an ending member, and a keyword (either the default INCLUSIVE or EXCLUSIVE). You can om.it either but not both ends of the range
The following example uses both ends of the range:
SELECT NON EMPTY DateOfSale.YearSold.MEMBERS ON 1 FROM holefoods
WHERE %TIMERANGE(DateOfSale.YearSold.&[2009],DateOfSale.YearSold.&[2011])
me
1 2009 179
2 2010 203
3 2011 224
The next example shows another open-ended range, this time using the EXCLUSIVE keyword:
SELECT NON EMPTY DateOfSale.YearSold.MEMBERS ON 1 FROM holefoods
WHERE %TIMERANGE(,DateOfSale.YearSold.&[2009],EXCLUSIVE)
1 2007 124
2 2008 156
