%TIMEWINDOW (MDX)
Returned Type
Syntax and Details
%TIMEWINDOW(periodSet,rangeTemplateStart)
Or:
%TIMEWINDOW(periodSet,rangeTemplateStart,rangeTemplateEnd)
Where:
-
rangeTemplateStart is a member of a time level within the same hierarchy, at a lower level than periodSet.
-
rangeTemplateEnd is another member of a time level within the same hierarchy, at a lower level than periodSet. If specified, rangeTemplateEnd must fall within the same period as rangeTemplateStart (for example, these two members must belong to the same year or to the same month).
The default for rangeTemplateEnd is rangeTemplateStart.
The system generates the set of members from rangeTemplateStart to rangeTemplateEnd and then uses that as a template to specify a time window. For example, if rangeTemplateStart is January 2000, and rangeTemplateEnd is June 2000, the time window consists of the dates from 1 January to 30 June of any given year.
Then the function examines each member of the given periodSet and, for each, returns the child members that fall within the given time window.
This function is intended for use within the WHERE clause or the %FILTER clause. It includes optimizations to return members of higher time levels where possible, so that large numbers of members are not returned.
Example
First, the following query uses %TIMEWINDOW as rows. This query examines birth years and for each one, selects only the patients born between 1 January and 5 January, inclusive:
SELECT NON EMPTY %TIMEWINDOW(birthd.year.MEMBERS,birthd.[jan 01 1924],birthd.[jan 05 1924]) ON 1
FROM patients
1 Jan 4 1918 1
2 Jan 3 1934 1
3 Jan 3 1937 1
4 Jan 4 1937 1
5 Jan 2 1938 1
6 Jan 1 1940 1
7 Jan 1 1941 1
8 Jan 4 1947 1
9 Jan 5 1947 1
10 Jan 2 1949 1
11 Jan 1 1953 1
...
In this example, the range template arbitrarily refers to dates in the year 1924; any year could be used instead.
As noted earlier, this function is primarily meant for use in filtering. The following query simply selects all patients born between 1 January and 5 January of any given year:
SELECT MEASURES.[%COUNT] ON 0 FROM patients
WHERE %TIMEWINDOW(birthd.year.MEMBERS,birthd.[jan 01 1924],birthd.[jan05 1924])
Patient Count
806
The following query uses the same filter but displays patients grouped by birth years:
SELECT MEASURES.[%COUNT] ON 0, NON EMPTY birthd.year.MEMBERS on 1
FROM patients
WHERE %TIMEWINDOW(birthd.year.MEMBERS,birthd.[jan 01 1924],birthd.[jan05 1924])
Patient Count
1 1918 1
2 1934 1
3 1937 2
4 1938 1
5 1940 1
6 1941 1
7 1947 2
8 1949 1
9 1953 1
...
To make the result more understandable, the following query uses %LABEL to apply a better caption:
SELECT %LABEL(MEASURES.[%COUNT],"Born Jan 1-5") ON 0, NON EMPTY birthd.year.MEMBERS on 1
FROM patients
WHERE %TIMEWINDOW(birthd.year.MEMBERS,birthd.[jan 01 1924],birthd.[jan 05 1924])
Born Jan 1-5
1 1918 1
2 1934 1
3 1937 2
4 1938 1
5 1940 1
6 1941 1
7 1947 2
8 1949 1
9 1953 1
...