IBM Cognos Analytics Tutorial

Month to Date or MTD Report

In this article, we will learn How to create Month to Date or MTD report in IBM Cognos Analytics Report?
In this article, we will focus on

1. Month to Date (MTD) Calculation and Concept
2. Month to Date (MTD) Report
3. Compare data between the current month and the previous month.

Following is the image for the demo report which we will create in this article. 

Data
We are using vLemon visitor database for the year 2018. Following is the download URL of the excel file for the same.

FileTypeSizeDownload
vLemonVisitors.csvNA5389038Login to Download

In this file, we have each visitor visit date, time and country.

Prerequisite 
You need an FM Package or a Data Module for the above data. We already published it. We didn't add any business logic in the FM or Data Module layer. We will add all the calculations in the reporting layer.

What is Month to Date or MTD?
Month to Date report or MTD refers to the period beginning the first day of the current month up to the current date. We need to calculate the date based on the current date.

Concept
We will find the current year and current month. Then we will use the current year and current month to filter out the data.

First, we need to find the current date. In Cognos to get the current date, we use current_date.
current_date: It returns a date value representing the current date of the computer that the database software runs on.
Example: current_date
Result: 2018-03-04
We have the current date next we need to get the current year using the current date. To get the current year we will use Cognos _year function.

_year(date)
Returns the value of the year field in "date_expression".
Example: _year (2018-07-29)
Result: 2018

We have the year. Next we need to find the current month. To find the current month we will use another Cognos function _month.

_month (date)
Returns the value of the month field in "date_expression".
Example: _month (2018-07-29)
Result: 7

We have year and month value. Now we can use it get the data for the data for the curent month of the year. Create a new query for Current Month Data with following filters.

Year Filter

_year(current_date) = _year([C].[vLemonVisitors].[vLemonVisitors_csv].[VisitDate])

Month Filter

_month(current_date) = _month([C].[vLemonVisitors].[vLemonVisitors_csv].[VisitDate])

Our Above query will return the data for the current month. Next, we need a query to get the Last month data. To get the last month data we need last month number. We will use current_date get the last month information. To calculate the last manth we need to first find the first date of the current mont. Cognos provide a function to calculate the first date of the current month. _first_of_month will return the first date of the month.

_first_of_month (date)
Returns a date or datetime, depending on the argument, by converting "date_expression" to a date with the same year and month but with the day set to 1.
Example: _first_of_month (2018-07-29)
Result: 2018-07-01

Next, we will use _add_days function to subtract one day from the month first date. It will return the last date of the last month. Then again we use _month to get the month number.

_add_days (date, integer_expression)
Returns the date or date time, depending on the format of "date", that results from adding "integer_expression" days to "date".
Add Days
Example: _add_days (2018-07-29, 1)
Result: 2018-07-30
Subtract Days
Example: _add_days (2018-07-29, -1)
Result: 2018-07-28

Following is the step by step calculation

_first_of_mont(current_date) => _first_of_mont(2018-07-29) = 2018-07-01

_add_days(_first_of_mont(current_date),-1)=>_add_days(2018-07-01,-1)=2018-06-30

_month(_add_days(_first_of_mont(current_date),-1))=>_month(2018-06-30) = 6

Now, we have last month. Use the last month date to find the year for the last month. We are in the middle of the year so the year for last month is same. Create a copy of the first query change the filter  with the following expression.

Year Filter

_year(_add_days(_first_of_mont(current_date),-1)) = _year([C].[vLemonVisitors].[vLemonVisitors_csv].[VisitDate])

Month Filter

_month(_add_days(_first_of_mont(current_date),-1)) = _month([C].[vLemonVisitors].[vLemonVisitors_csv].[VisitDate])

Finally, create a union of the two query. Use the union query for the line chare and the Cross Tab.

...

About The Author

Hello, I am Mayank Sanghvi I am primarily a BI developer with experience in MSBI and Cognos BI Suit. Also having interest in various other technologies such as Android, Java, C#, ASP .NET and other. I enjoy learning new technologies and share my experience with others.

0 Comments
Leave A Comment

Please login to post your valuable comments.

add