To use IBM Cognos product documentation, you must enable JavaScript in your browser.

Setting Up Relative Time Categories

Some of the most commonly requested reports in any organization are period-over-period performance reports and trend analyses, how sales in the current period compare to sales from previous periods, and how last year's budget compares to projections for next year. By including the most commonly required relative time categories in your cubes, you avoid the need to recalculate them in every OLAP report.

Relative Time Categories

Models can contain three different types of relative time categories:

You can also create custom to-date and N-period relative time categories to span specified time ranges.

When using relative time categories, ensure that you set the Inclusion property to Always include for each level in the time dimension so that the relative dates are calculated correctly.

Default Relative Time Categories

The following relative time categories are automatically inserted into your model. However, you can delete them or replace them with custom time categories that better meet your users' OLAP reporting needs.

Tip: To replace automatically generated relative time categories with custom time categories, start with the closest built-in choice, change it to a custom relative time category and then change only those few settings needed to customize the category.

The periods depend on the date levels defined in your model.

In the Transformer Categories diagram, relative time periods appear in the category viewer as special categories, highlighted in pale green, below the other drill-down paths in the time dimension. Grouped special categories only appear in reports when the time dimension contains one or more levels higher than the current period.

Set the Current Period Automatically

Although the current period is set automatically, you should explicitly select the column to use from the Data Sources list. If two or more data sources contain columns that provide date values, you will avoid ambiguity by explicitly clearing the Sets the current period check box in the other Data Source property sheets.

Steps
  1. In the Data Sources list, successively open the property sheet for each data source containing a date column for the time dimension.

  2. On the General tab, clear the Sets the current period check box for all but the one you want used to set the current period.

  3. Open the property sheet for the time dimension and, on the Time tab, select the Automatically set the current time period check box. Click OK.

Set the Current Period Manually

If you clear the Automatically set the current time period check box, you can set the current period to use any date category in the time dimension.

In general, you select a date category that appears at the lowest level in the time dimension, but this is not a mandatory requirement. Once set, each time category is generated and, before the creation of any cubes, the latest date category at the lowest level below the selected category is used to set the current period. This feature is useful when you want to automatically set the current period to the latest date in a year, rather than to the latest date in your data.

Steps
  1. Open the property sheet for the time dimension and, on the Time tab, clear the Automatically set the current time period check box. Click OK, and open the Categories diagram for the time dimension.

  2. Select the date category that you want to be the current period and, from the Diagram menu, click Set Current Period.

    The current period is set for the time dimension and automatically updated on the time dimension property sheet.

Track Changes in a Measure over a Specific Time Period

Businesses often need to track changes in a measure, from one time period to another.

For example, suppose your users want to gauge current sales revenues in light of previous performance. You set up the current period for your model to automatically use the most recent date values available after each data update. Then, you set up relative time categories that represent last month, last quarter, and last year, as well as the same month of the prior quarter, same quarter of the prior year, and same month of the prior year. Your OLAP report users can now analyze trends from month to month, quarter to quarter, and year to year.

Steps
  1. Open the Categories diagram for the time dimension and position the pointer over the right side of the root category.

    The pointer changes to a crosshair.

  2. Drag the pointer to the right of the root category.

    A new relative time category is created and its property sheet opens.

  3. In the Category code box, type a name for the new category.

  4. Click the Relative Time tab.

  5. In the Relative time box, select the relative time period that represents the single period for which you want to create a relative time category, and click OK.

    For a Year-Quarter-Month time dimension, the available single categories list includes the following:

    Note: Select Custom if you want to override any of the default selections on the Relative Time tab. For example, you can specify the basic approach, a different target time period for your new category, a new context in which the period will be reported, and the corresponding offsets, to track values that apply to a period other than the current one.

    For more information about specifying custom time periods, see Track Changes in a Measure over Several Time Periods and Track Changes in a Measure in Future Time Periods.

Track Changes in a Measure for a Period-to-Date

Businesses often need to track changes in a measure for a specific period to-date.

For example, suppose your users want to gauge current sales revenues in light of previous performance. You set up the current period for your model to automatically use the most recent date values available after each data update. Then, you set up the following relative time categories: Current Month, Quarter To-Date, Year To-Date, Year To-Date, and Life To-Date.. Your OLAP report users can now analyze growth trends for a full range of to-date time periods.

Steps
  1. Open the Categories diagram for the time dimension and position the pointer over the right side of the root category.

    The pointer changes to a crosshair.

  2. Drag the pointer to the right of the root category.

    A new relative time category is created and its property sheet opens.

  3. In the Category code box, type a name for the new category.

  4. Click the Relative Time tab.

  5. In the Relative time box, select the relative time period that represents the to-date period for which you want to create a relative time category and click OK.

    For a Year-Quarter-Month dimension, the available to-date categories list includes the following:

    Note: Select Custom if you want to override any of the default selections on the Relative Time tab. For example, you can specify the basic approach, a different target time period for your new category, a new context in which the period will be reported, and the corresponding offsets, to track values that apply to a period other than the current one.

    For more information about specifying custom time periods, see Track Changes in a Measure over Several Time Periods and Track Changes in a Measure in Future Time Periods.

Track Changes in a Measure over Several Time Periods

In some cases, you may want to set up relative time categories that span several specific time periods, at points in the past or the future. Several built-in relative time categories are available, to help or, if you have an unusual reporting period not covered by the automatically created relative time categories, you can create a custom category.

For example, suppose your users want to use relative time categories to track sales for prior months, quarters, and years. However, some users also want to track sales over six-month periods prior to the current date, in both the current year and the previous year. You set up an N-period running total category that spans the six months leading up to the current month, this year, and last year. Your OLAP report users can now analyze growth trends for the required time periods.

If you enter positive numbers for either the Target offset or the Context offset, your model must include source columns with time periods later than the current period, as positive values in these fields signal measures that are essentially forecasts, in future time periods.

Steps
  1. Open the Categories diagram for the time dimension and position the pointer over the right side of the root category.

    The pointer changes to a crosshair.

  2. Drag the pointer to the right of the root category.

    A new relative time category is created and its property sheet opens.

  3. In the Category code box, type a meaningful name for the new category, such as Previous 6 Months.

  4. Click the Relative Time tab.

  5. In the Relative time box, select Custom.

  6. In the Basic approach box, select N-Period Running Total or N-Period Running Total (Grouped).

    Grouped lets you easily create a series of categories spanning different ranges of time.

  7. In the Number of periods box, type the number of periods to include.

    For example, if you are creating a 6-month running total, type the number 6.

  8. In the Target period box, select the type of period for which the N-period running total will be kept.

    For example, if you are creating a 6-month running total, select Month.

  9. In the Target offset box, type a number that reflects an offset for the Target period relative to the current period.

    For example, if the current period is December 2006 and you want a 6-month running total up to (ending) November 2006, type -1.

  10. In the Context period box, select a time period one or more levels higher than the Target period, within which you want to calculate the N-period running total.

    For example, if you are creating a running total of 6 months, create the total within the context of Year.

  11. In the Context offset box, or in the Context range box if you selected N-Period Running Total (Grouped) in the Basic approach box, type the number by which the Context period is offset when the N-period running total is created.

    For example, if you are creating a 6-month running total relative to a Target period of last year, type -1.

    Your relative time dimension now contains the specified running-total time periods.

Track Changes in a Measure in Future Time Periods

If your source data contains forecast values (that is, source columns with time periods and date values that are later than the current period), you can add relative time categories such as Next Quarter or Next Year to your model to report future projections for the applicable measures.

For example, suppose the current period is December 31, 2006, but a data source containing sales forecasts for all four quarters of 2007 is included in the Data Sources list for the model. You create relative time categories with positive target or context offsets, to track the Next Year and Next Quarter projections.

Steps
  1. Open the Categories diagram for the time dimension and position the pointer over the right side of the root category.

    The pointer changes to a crosshair.

  2. Drag the pointer to the right of the root category.

    A new relative time category is created and its property sheet opens.

  3. In the Category code box, type a meaningful name for the new category, such as Next Month.

  4. Click the Relative Time tab.

  5. In the Relative time box, select Custom.

  6. In the Basic approach box, select the option that matches the kind of projection that you want.

    Depending on your selected approach, different controls appear on the Relative Time tab. For a current period of December, the settings for three typical future time periods are as follows:

    Basic Approach: CategorySettings
    Single Category: Next MonthTarget period=Month; Target offset=1
    Period To-Date Total: Year-to-date, Next YearTo-Date Period=Year; Target period=Month; Target offset=0; Context period=Year; Context offset=1
    N-Period Running Total: First 6 Months, Next YearNumber of periods=6; Target period=Month; Target offset=-6; Context period=Year; Context offset=1

  7. After you have set all the properties for the relative time category, click OK.

    Your relative time dimension now contains the specified future time periods.

Track Monthly Performance Measures in Different Time Periods

Business users often want to see monthly performance in one time period compared to another. You can add the extra time dimension to your model by using the calculated columns feature. This solution doesn't require a change to the date columns in your source data.

For example, you can use a calculated column to model monthly sales revenues, comparing the performance in 2006 to that in 2007. Your users can see a graphic representation of their results in their OLAP reporting component.

Steps
  1. Add a calculated column to your model.

    For more information, see Define a Calculated Column.

  2. When prompted to select a data class for your calculated column in the Column Data Class dialog box, select Numeric and click OK.

  3. In the Column Calculation dialog box, enter the following formula: month(<Date>) where <Date> is the column used to build your time dimension.

  4. Click OK twice.

    The newly created calculated column appears as a new column in the data source.

  5. Drag the new calculated column from the Data Sources list to the Dimension Map to create a new dimension.

  6. Add the required levels, dimensions, and measures to your model and create the cube.

    For more information about creating cubes, see Create a Single PowerCube.