DeepSee MDX Reference
%TIMEWINDOW
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Returns a set of members of a time dimension that match the given range template.
Returned Type
This function returns a set of members.
Syntax and Details
%TIMEWINDOW(periodSet,rangeTemplateStart)
Or:
%TIMEWINDOW(periodSet,rangeTemplateStart,rangeTemplateEnd)
Where:
DeepSee 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
...