IBM Cognos Analytics Tutorial

Week to Date or WTD Report

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

1. Week to Date (WTD) Calculation and Concept
2. Week to Date (WTD) Report
3. Compare data between the current week and the previous week.

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.

FileDownload
vLemonVisitors.csvLogin 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 Week to Date or WTD?
Week to Date report or WTD refers to the period beginning the first day of the current week up to the current date. We need to calculate the date based on the start and the end day of the week.

Concept
First, we need to calculate the Start and the End date for the current week. As per the requirement, the start day of a week is Monday and Sunday is the End Day.

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 day of the week using the current date. To get the day of the week, we will use the _day_of_week function.

_day_of_week (date, integer) 
Returns the day of the week (1 to 7), where 1 is the first day of the week as indicated by the second parameter (1 to 7, 1 being Monday and 7 being Sunday). Note that in ISO 8601 standard, a week begins with Monday being day 1.
Example: _day_of_week (2018-07-29, 1)
Result: 7

Monday is our starting day of the week so our expression to find out the day of the week is _day_of_week (current_date, 1). Just for explanation, let's assume Sunday is the starting day of the week. Then we need to modify our expression to _day_of_week (current_date, 7). It will return 1 for the date 2018-07-29 (29-July-2018 Sunday). We will use _day_of_week (current_date, 1) Why? Because of the current requirement, Monday is the starting day of the week. We have the day of the week. Next, we need to find the start date of the week. To calculate the Start Date, we will use the _add_days function.

_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

In the _add_days function, we are providing a date and the integer value to add the number of days. If the integer is positive the function will add the days. With the negative integer, it will subtract the days.
We are using a current_date to get the current date. We need to find the historic date so we will use a negative integer.

Now, what will the value of the integer to get the start date of the week?
At the time of writing this article the date the 29-July2018. It is the 7 the day of the current week. If we minus the 6 days from the current date we will get the date for Monday (23+6 = 29). Let's modify our expression to get the start date of the current week.
Following is the final expression to get the start date _add_days (current_date, (_day_of_week (current_date, 1)-1) * -1).

Let's break the above expression and understand what we are doing here.

current_date = 29-July-2018
_day_of_week (current_date, 1) => _day_of_week (2018-07-29,1) = 7
(_day_of_week (current_date, 1)-1) => (7-1) = 6
(_day_of_week (current_date, 1)-1) * -1 => 6 * -1 = -6
_add_days( current_date, (_day_of_week (current_date,1)-1) * -1) => _add_days( 2018-07-29, -6) = 2018-07-23

We got the Start date of the week. Next, we need to use it in the filter to get the records for the current week.

Code

cast([C].[vLemonVisitors].[vLemonVisitors_csv].[VisitDate], date) BETWEEN
cast(_add_days
( 
	current_date
	, (
		(
			_day_of_week 
			(
				current_date
				,1
			)
			-1  
		)* -1
	) 
),date)
AND 
cast(current_date,date)

 

Above filter is for first or the current week data. For the last week data, we need to modify the filter conditions. Following is the modified version of the filter to get data for the last week.

cast([C].[vLemonVisitors].[vLemonVisitors_csv].[VisitDate], date) BETWEEN
cast(_add_days
( 
	current_date
	, (
		(
			_day_of_week 
			(
				current_date
				,1
			)
			+6 
		)* -1
	) 
),date)
AND 
cast(_add_days ( current_date , ( ( _day_of_week ( current_date ,1 )  )* -1 ) ),date)

 

...

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