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.
We are using vLemon visitor database for the year 2018. Following is the download URL of the excel file for the same.
|vLemonVisitors.csv||NA||5389038||Login to Download|
In this file, we have each visitor visit date, time and country.
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.
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.
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.
Returns the value of the year field in "date_expression".
Example: _year (2018-07-29)
We have the year. Next we need to find the current month. To find the current month we will use another Cognos function _month.
Returns the value of the month field in "date_expression".
Example: _month (2018-07-29)
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(current_date) = _year([C].[vLemonVisitors].[vLemonVisitors_csv].[VisitDate])
_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.
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)
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".
Example: _add_days (2018-07-29, 1)
Example: _add_days (2018-07-29, -1)
Following is the step by step calculation
_first_of_mont(current_date) => _first_of_mont(2018-07-29) = 2018-07-01
_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(_add_days(_first_of_mont(current_date),-1)) = _year([C].[vLemonVisitors].[vLemonVisitors_csv].[VisitDate])
_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.
Please login to post your valuable comments.