Data modules

Relative Dates are a powerful feature of Cognos Analytics data modules that enable quick and flexible comparisons between time periods. For example, comparing revenue for the current year version the previous year, or the current week of the month vs the same week last month. These comparisons are popular across all lines of business but especially in the office of finance, which relies on period over period analytics as a bedrock of analysis. The video below covers how to set up and use relative dates in Cognos Analytics.

Relative dates are a feature exclusive to data modules. If you want to use relative dates in Framework Manager, your best bet is to extract the necessary fields into a data set. You can then import the data set into a data module and set up relative dates there. In all likelihood you’ll also get better query performance because data sets are great!

Setting up relative dates

There are two components to the relative date functionality in Cognos Analytics, Relative date filters and relative date measures. They are both easy to configure and use. To set them up you need the following:

• The ability to use data modules
• Access to the ‘Calendars’ folder that ships with Cognos
• Data that includes a ‘date’ or ‘date/time’ data type column
• The ability to use the linked module feature of Cognos

Your administrator can turn on the relevant permissions. Once that’s done, it’s simply a matter of following the steps in the video above, but I’ll summarize them here:

1. Open the data module that requires relative dates
2. Slick the ‘add source’ button
3. Navigate to and select the calendar you want. Cognos ships with three by default, Fiscal, Gregorian and 5-4-5. You can create custom calendars as well.
4. Your data module will now contain a new linked calendar table. This table is easy to recognize as it will be slightly transparent and turquoise.
5. Now select any date or date/time column in your data and open the properties. Select your calendar table under the ‘lookup reference’ property.
6. Congratulations, you have configured your relative date filters!
7. Now select any measure column in your data and open the properties. Select the date you just configured in step 5 under the ‘lookup reference’ property.
8. Congratulations again, you have configured your relative date measures!
9. Click on the arrow next to either column to see the filters or measures you created.

Relative date filters

Relative date filters provide drag and drop capability to instantly filter any visualization, table or crosstab to the selected period. By default there are 23 relative date filters, but you can create more by editing the calendar files that ship with Cognos.

Relative date measures

Relative date measures are similar to relative date filters but represent the measure value on a visualization rather than a filter. Think of it as the line on a line chart. There are 23 relative date measures by default but by all means, customize.

Relative dates in action

If you’re having a difficult time picturing the final product of relative dates, here’s a simple chart to make it easy to understand how useful they are:

Above you can see a common but powerful analysis. On the X axis we have the months of the year, and on the Y revenue totals. Using relative date measures I can easily plot two lines, one for current year and one for previous year for comparison. I am able to do this with zero coding or SQL. As a bonus, these relative periods will always be up to date – when the year rolls over into 2022, the value of Prior Year automatically updates to 2021.

As-of-date analysis

By default Cognos Analytics assumes the current date as the point of reference for relative date analysis. Therefore, the final piece of the relative date puzzle is enabling as-of-date analysis. This global parameter allows users to select the ‘as of date’ for the relative time feature. It’s easiest to think of this by asking yourself, relative to what date am I counting ‘year to date’ or ‘month to date’. Is it today? Last Friday? December 19th, 1982?

Configuring the as-of-date global parameter is a task for administrators and is very easy:

1. Open the manage menu and select ‘Customization’
2. Select the ‘Parameters’ tab
3. Click ‘New’
4. Type ‘_as_of_date’
5. Select the ‘More’ menu (the ellipsis that appear when you hover over the parameter) and click ‘Properties’
6. Click ‘Applied to all roles’ if you want everyone to have this capability
7. Log out of Cognos
8. Log back in to see the global parameters button appear in the upper-right corner of the UI

Cognos relative dates in summary

There you have it. Reading back over this blog post there are a lot of steps included, but don’t get intimidated. This is remarkably easy compared to writing relative date calculations or using SQL to do it. The video above walks through every single step included in this blog post so watch and follow along. And don’t forget to like and subscribe to the Super Data Brothers channel!

IBM documentation references

Relative date documentation home
Sample calendars
Creating a data module for relative date analysis
Creating relative date filters
Customizing the reference date

---

Keep reading for more awesome Cognos content!

• Cognos Union Queries in Reports
• Cognos Relative Dates in 11.2
• The 2021 Gartner BI Magic Quadrant is Broken for Cognos Analytics
• Data Modeling for Success: BACon 2020
• Cognos Analytics 11.1.6 What’s New