Time Series Functions

  • Article

The set of time series functions in MDX provides a powerful tool for data analysis. The time series functions described in this section are member, set, and numeric functions.

Even though these functions are called time series functions, they work equally well with any other dimension. This is because the semantics of these functions do not rely on the underlying dimension being the Time dimension. In many cases, scenarios exist where they can be useful on other dimensions. However, by far their most common use is with the Time dimension, hence their name. The exceptions to this are the xTD (YTD, MTD, QTD, WTD) functions, which are applicable to the Time dimension only.

Note

As used here, Time dimension does not necessarily refer to a dimension whose name is "Time." Rather, it refers to the dimension whose DIMENSION_TYPE property in the DIMENSIONS rowset is MD_DIMTYPE_TIME.

The time series functions operate on the dimension that is specified implicitly as part of their <set>, <member>, or <level> arguments. However, these arguments are optional for many time series functions, so it is possible to invoke the function with no <set>, <member>, or <level>arguments. In such a case, the provider applies this function to that dimension whose DIMENSION_TYPE property is MD_DIMTYPE_TIME. If there exist multiple dimensions whose DIMENSION_TYPE property is MD_DIMTYPE_TIME, whichever is chosen is provider-specific.

Time Series Set Value Expressions

LASTPERIODS(<index>[, <member>])

Returns the set of <index> periods ending with <member> and starting with the member lagging <index> ? 1 from <member>. This is the same as <member>.LAG(<member>,<index> ? 1**)**:<member>.

For example, the statement

LASTPERIODS(5, [1991June])

returns the set

{[1991Feb], [1991Mar], [1991Apr], [1991May], [1991June]}

If <member> is not specified, its value is Time.CURRENTMEMBER.

PERIODSTODATE([<level>[, <member>]]))

Within the scope of <level>, returns the set of periods on the level of <member>, starting with the first period and ending with <member>. If no levelor member is specified, the <member> value is Time.CURRENTMEMBER and <level> is the parent level of Time.CURRENTMEMBER. If a level is specified, then <member> is <dimension>.CURRENTMEMBER, where <dimension>is the dimension of <level>.

The statement

PERIODSTODATE(Quarter, [05-Sep-1997])

returns the set of days from the beginning of Quarter3. (This is the member at the Quarter level that is the ancestor of [05-Sep-1997]) through [05-Sep-1997].)

And the statement

PERIODSTODATE(Year)

returns the set of members through Time.CURRENTMEMBER, from the beginning of the year that is the ancestor of Time.CURRENTMEMBER.

The statement

PERIODSTODATE()

returns the set of members from the beginning of the containing period of Time.CURRENTMEMBER to Time.CURRENTMEMBER. All the returned members are at the same level as Time.CURRENTMEMBER.

PERIODSTODATE(<level>, <member>) is the same as TOPCOUNT(Descendants(Ancestor(<level>, <member>), <member>.Level), 1**):<**member>.

xTD([<member>])

A shortcut function to PERIODSTODATE that predefines the <level> argument to be Year (YTD), Month (MTD), Quarter (QTD), or Week (WTD). If no member is specified, the default is Time.CURRENTMEMBER.

Time Series Member Value Expressions

OPENINGPERIOD([<level>[, <member>]])

This function returns the first period among the descendants of <member> at <level>. For example, OPENINGPERIOD(Month, [1991]) returns [1991January]. If no <member> is specified, the default is Time.CURRENTMEMBER. If no <level> is specified, it is the level below that of <member>. This function is equivalent to TOPCOUNT(Descendants(,<member>, <level>), 1**)**.

The function CLOSINGPERIOD is very similar, the only difference being that it returns the last sibling instead of the first sibling.

PARALLELPERIOD([<level>[, <index>[, <member>]]])

This function is similar to the COUSIN function but is more closely related to the time series functions. It takes the ancestor of <member> at <level> (call it <ancestor>), and then it takes the sibling of <ancestor> that lags by <index> and returns the parallel period of <member> among the descendants of <ancestor>.

This function has the following defaults:

  • Default <member> value is Time.CURRENTMEMBER if <level> is not specified. Otherwise, it is <dimension>.CURRENTMEMBER, where <dimension> is the dimension to which <level> belongs.

  • Default <index> is 1.

  • Default <level> is the level of the parent of <member>.

This function is equivalent to the following:

Note

COUSIN(<member>,LAG(ANCESTOR(<member>,<level>),<index>)

The expression

PARALLELPERIOD(Year,2, [96 Qtr 3])

returns the member [94 Qtr3].

The expression

PARALLELPERIOD(Year,2)

returns the parallel period of Time.CURRENTMEMBER of two years ago. That is, if Time.CURRENTMEMBERis [1993June], the returned member is [1991June].

The expression

PARALLELPERIOD(Year)

returns the parallel period of Time.CURRENTMEMBER from last year. That is, if Time.CURRENTMEMBER is [1993June], the returned member is [1992June].

The expression

PARALLELPERIOD()

returns the parallel period in the immediately prior sibling to the parent of Time.CURRENTMEMBER. For example, if Time.CURRENTMEMBER is [1993June], the returned member is [1993March]. The parent of ([1993June] is Qtr2, whose immediately prior sibling is Qtr1, in which the parallel period is [1993March].