The set of time series functions in MDX provides a powerful tool for data analysis. The time series functions described in this section are, in fact, member, set, or 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 fact, in many cases, there exist actual scenarios 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.
It is useful to clarify what we mean by “Time dimension.” This 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, then which one is chosen is provider specific.
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 level or member is specified, then 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.
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].)
PERIODSTODATE(Year)
returns the set of members from the beginning of the year that is the ancestor of Time.CURRENTMEMBER, through Time.CURRENTMEMBER.
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(member, level), 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, then the default is Time.CURRENTMEMBER.
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 LAG(member, index – 1):member.
For example,
LASTPERIODS(5, [1991June])
returns the set
{[1991Feb], [1991Mar], [1991Apr], [1991May], [1991June]}
If member is not specified, then it is Time.CURRENTMEMBER.
PARALLELPERIOD([<level>[, <index>[, <member>]]])
This function is similar to the COUSIN function, but is more closely related to Time series. It takes the ancestor of member at level (call it ancestor); 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:
This function is equivalent to:
COUSIN(member,LAG(ANCESTOR(member,level),index)
PARALLELPERIOD(Year,2, [96 Qtr 3])
returns the member [94 Qtr 3].
PARALLELPERIOD(Year,2)
returns the parallel period of Time.CURRENTMEMBER, two years ago. That is, if Time.CURRENTMEMBER is [1993June], then the returned member is [1991June].
PARALLELPERIOD(Year)
returns the parallel period of Time.CURRENTMEMBER from last year. That is, if Time.CURRENTMEMBER is [1993June], then the returned member is [1992June].
PARALLELPERIOD()
returns the parallel period in the immediately prior sibling to the parent of Time.CURRENTMEMBER. For example, if Time.CURRENTMEMBER is [1993June], then the returned member is [1993March]. The parent of ([1993June] is Quarter2, whose immediately prior sibling is Quarter1, in which the parallel period is [1993March].
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, then the default is Time.CURRENTMEMBER. If no level is specified, then 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 it returns the last sibling instead of the first sibling.
These are functions pertaining to finding covariance, correlation, and linear regression by using the least squares method.
COVARIANCE(<set>, <numeric_value_expression>
[, <numeric_value_expression>])
CORRELATION(<set>, <numeric_value_expression>
[, <numeric_value_expression>])
Covariance and correlation are measures of linear association. Both functions evaluate set against the first numeric value expression to get the values for the y-axis. The set is evaluated against the second numeric value expression (if present) to get the set of values for the x-axis. If the second numeric value expression is not present, then the members of set are used as values for the x-axis.
The latter case does not make much sense for most dimensions (for example, SalesPerson). However, it does make sense for the Time dimension. Covariance and correlation are calculated for these x and y values.
Linear regression by using the least squares method calculates the equation of the best fit line for a series of points. For the purposes of this section, let the regression line be given by the equation
y = ax+b
where a is called the intercept and b is called the slope.
LINREGINTERCEPT(<set>, <numeric_value_expression>
[, <numeric_value_expression>])
LINREGSLOPE(<set>, <numeric_value_expression>
[, <numeric_value_expression>])
LINREGVARIANCE(<set>, <numeric_value_expression>
[, <numeric_value_expression>])
LINREGR2(<set>, <numeric_value_expression>
[, <numeric_value_expression>])
Evaluates set against the first numeric value expression to get the set of values for the y-axis. Evaluates set against the second numeric value expression (if present) to get the set of values for the x-axis. If the second numeric value expression is not present, then it uses the members of set as values for the x-axis.
The latter case does not make much sense for most dimensions (for example, SalesPerson). However, it does make sense for the time dimension.
After obtaining the set of points, LINREGINTERCEPT returns the intercept of the regression line (b in the equation above). LINREGSLOPE returns the slope of the regression line (a in the equation above). LINREGVARIANCE returns the statistical variance that describes the fit of the linear equation to the points, and LINREGR2 returns the statistical R2 that describes the fit of the linear equation to the points.
LINREGPOINT(<numeric_value_expression>, <set>,
<numeric_value_expression> [, <numeric_value_expression>])
Uses the last three arguments — set and numeric_value_expression — like the other LINREGxxx functions to calculate the regression line. Now, it evaluates the first argument (numeric_value_expression) and uses the resulting number as the “x” value in the regression equation (y = ax + b) to calculate the “y” value.
For example, the dimension “Campaign” contains members that represent the various advertising campaigns. There are two measures associated with each campaign — the cost of the campaign and the revenues resulting from the campaign. To find the correlation between the cost of the campaign and the revenues that it generates, you use the regression line y = ax + b, where x is the cost and y is the revenue. The a and b values are calculated by using:
a = LinRegCoef(Campaigns.Members, Revenues, Cost)
b = LinRegConst(Campaigns.Members, Revenues, Cost)
For another example, you can find the trend in Sales for the sales of the last nine periods and forecast the next period:
a = LinRegSlope(LastPeriods(9), Sales)
b = LinRegIntercept(LastPeriods(9), Sales)
NextPeriod = LinRegPoint(10, LastPeriods(9), Sales)