Using a relative time period calculation in a cross tab
Cross tabs often aggregate and display data by time periods because time is an essential part of data analysis. Any analysis of stock performance, revenue, or productivity is meaningful only if it can be measured by day, week, month, quarter, or year. For example, a cross tab can display revenue data for a particular month, quarter, or year. Figure 4-17 displays sales revenue by region by quarter for two years.
Figure 4-17  
Also, a cross tab supports relative time periods, such as current month, current quarter, current year, previous month, previous quarter, and previous year. Displaying data by relative time periods supports, for example, the comparison of current data with past data of the same period. Figure 4-18 shows quantity ordered year-to-date 2005 values, using the date 06/30/2005, and the comparable year-to-date values for the previous year for Spain and USA.
Figure 4-18  
Supported time periods
Table 4-3 describes the time periods available for the Time Period property. All the time periods are relative to a reference date, which can be the current date defined as the date the report runs, a date you specify, or the latest date in the time dimension.
Table 4-3  
The entire month relative to the month and year portions of the reference date. For example, if the reference date is 2011-01-10, the period is 2011-01-01 to 2011-01-31.
The entire period from a specified period relative to the reference date. For example, use to aggregate data for the same quarter five years ago.
*
The First Period, which specifies year, quarter, or month as the type of period for which to aggregate data
*
Number of Periods Ago, which specifies the number of prior periods (type of period specified next), from which to begin the calculation
*
The Second Period, which specifies year, quarter, month, or day as the type of period
For example, if the reference date is 2012-02-08, to aggregate data for the same quarter five years ago, specify the following:
The entire quarter relative to the month and year portions of the reference date. For example, if the reference date is 2011-12-15, the period is 2011-10-01 to 2011-12-31.
The entire year relative to the year portion of the reference date. For example, if the reference date is 2011-06-30, the period is 2011-01-01 to 2011-12-31.
The period starting at the beginning of the reference date’s month and ending at the reference date. For example, if the reference date is 2011-12-25, the period is 2011-12-01 to 2011-12-25.
Same as Month To Date, but for the previous nth year. This time period requires another property, Number of Years Ago, which specifies which prior year. For example, if the reference date is 2011-12-25 and Number of Years Ago is 1, the period is 2010-12-01 to 2010-12-25.
The next n periods from the reference date. This time period requires two additional properties:
*
The First Period, which specifies year, quarter, month, week, or day as the period in which to begin the calculation
For example if the reference date is 2012-01-01, Number of Periods Ago is 1, and The First Period is Month, then the period is 2012-01-01 to 2012-01-31.
The period from a specified period relative to the reference date. For example, use to aggregate data for the same quarter to date, five years ago.
*
The First Period, which specifies either year, quarter, or month as the type of period for which to aggregate data
*
Number of Periods Ago, which specifies the number of prior periods (type of period specified next), from which to begin the calculation
For example, if the reference date is 2012-02-08, to aggregate data for the same quarter up to 02-08, five years ago, specify the following:
Data is aggregated for 2007-01-01 to 2007-02-08.
The previous nth month relative to the month and year portion of the reference date. The day portion is ignored. This time period requires another property, Number of Months Ago, which specifies which prior month. For example, to specify three months back from the reference month, type 3. If the reference date is 2012-01-15 and N Months Ago is 3, then the period is 2011-10-01 to 2011-10-31.
The previous nth month relative to the reference date. This time period requires another property, Number of Months Ago, which specifies which prior month. For example, to specify three months back from the reference date, type 3. If the reference date is 2012-01-15 and Number of Months Ago is 3, then the period is 2011-10-01 to 2011-10-15.
The previous nth quarter relative to the month and year portion of the reference date. The day portion is ignored. This time period requires another property, Number of Quarters Ago, which specifies which prior quarter. For example, to specify one quarter back from the reference date, type 1. If the reference date is 2012-03-15 and Number of Quarters Ago is 1, then the period is 2011-10-01 to 2011-12-31.
The previous nth quarter relative to the reference date. This time period requires another property, Number of Quarters Ago, which specifies which prior quarter. For example, to specify one quarter back from the reference date, type 1. If the reference date is 2012-03-15 and Number of Quarters Ago is 1, then the period is 2011-10-01 to 2011-12-15.
The previous nth year relative to the year portion of the reference date. This time period requires another property, Number of Years Ago, which specifies which prior year. For example, to specify two years back from the reference date, type 2. If the reference date is 2011-09-30 and Number of Years Ago is 2, then the period is 2009-01-01 to 2009-12-31.
The previous nth year relative to the reference date. This time period requires another property, Number of Years Ago, which specifies which prior year. For example, to specify two years back from the reference date, type 2. If the reference date is 2011-09-30 and Number of Years Ago is 2, then the period is 2009-01-01 to 2009-09-30.
The period starting at the beginning of the reference date’s quarter and ending at the reference date. For example, if the reference date is 2011-12-25, the period is 2011-10-01 to 2011-12-25.
Same as Quarter To Date, but for the previous nth year. This time period requires another property, Number of Years Ago, which specifies which prior year. For example, if the reference date is 2011-12-25 and Number of Years Ago is 1, then the period is 2010-10-01 to 2010-12-25.
The last n days from the reference date. This time period requires another property, Number of Day(s) Ago, which specifies the number of trailing days. For example, if the reference date is 2011-12-25, and Number of Day(s) Ago is 15, then the period is 2011-12-10 to 2011-12-24.
The last n months from the reference date. This time period requires another property, Number of Months Ago, which specifies the number of trailing months. For example, if the reference date is 2011-12-25, and Number of Months Ago is 3, then the period is 2011-09-25 to 2011-12-25.
The last n periods from a specified period relative to the reference date. For example, use to aggregate data for the two months prior to the reference date five years ago.
This time period requires four additional properties. The first and second properties define the period for which to aggregate data:
*
The First Period, which specifies year, quarter, month, or day as the type of period
The third and fourth properties define the period relative to the reference date from which to begin the calculation.
*
The Second Period, which specifies either year, quarter, month, or day as the type of period
For example, if the reference date is 2012-01-01, to aggregate data for the two months prior to January 1 five years ago, specify the following:
The period starting at the beginning of the reference date’s year and ending at the reference date. For example, if the reference date is 2011-06-30, the period is 2011-01-01 to 2011-06-30.

Additional Links:

Copyright Actuate Corporation 2012