How to Work With Dates in Cognos Insight

Dealing with dates in Cognos can sometimes be a daunting task.  Dates should be straightforward, but sometimes they take extra work to get what you want.  Your data is designed to list the date of the transaction, but maybe you only care about data on a per week, per month, or per quarter basis.  Obviously, your data isn’t arranged according to these parameters, but it is quite easy to set your dates the way you want.

Let me demonstrate how to adjust dates, and get them to work just the way you want, by using some demo data.  I will start by assuming we have already imported our data.

• In the overview area I will select “Close_Date” for rows, and for the columns I will select our measures and select amount. Also in columns, I will select closed business only. My numbers look like this.

• This works great when we want only data.  But what if we just want last month, or just the last quarter?  It’s quite simple, just a few steps and you can have a dynamic date that will change with the current date.
• Expand the content pane by clicking the button in the top right.
• Right click the dimension you want to edit, mine will be Close_Date, and select Edit.
• On the right of copy and paste select the button called Time Roll Ups.                     
• I will select Current Quarter, Once this is done we can see all of our roll ups.  Click close to get back to our main workspace.

• Now in our overview area we can open close the date and select the current quarter.  We now only get the relevant information for this quarter.  As we enter a new quarter this information will update.
• For this current quarter, we can see that we have only closed two deals using a column chart.

• It is also nice to add a roll up for a past quarter.  In doing so you can review on how the current quarter relates to the past quarter.  I will repeat the steps above and add previous quarter.   You can now see that my current quarter is quite a bit under the previous quarter.  But we can say this is okay as we are only 1/3 of the way into the quarter.

We started working with data that only had individual dates.  We wanted to use these and collapse them to a per quarter basis.  We were able to set up roll ups to give us our current quarter and previous quarter.  Using a column chart we can visually see how the two quarters compare.

Dates are easy to work with and are one of the most important dimensions when dealing with data.  It’s always to know when your sales happen or how sales should look in the future.  Using these roll ups we can better achieve widgets that are useful and up to date every single time it is opened, and will not need maintenance.