In this article, we will learn How to create Week to Date or WTD report in IBM Cognos 10?
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.
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 for the above data set. We already published it. We didn't add any business logic in the FM 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.
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.
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)
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".
Example: _add_days (2018-07-29, 1)
Example: _add_days (2018-07-29, -1)
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 create a query to get the current week data. Use the above calculation to filter out the current week data. Following is the code for the current week data filter.
cast([Presentation].[Visitors].[VisitDate],date) BETWEEN cast(_add_days ( current_date , ( ( _day_of_week ( current_date ,1 ) -1 )* -1 ) ),date) AND cast(current_date,date)
We have data for the current week. Let’s assume we need to compare data between the current week and the last week. For this, we will need last week data. Create a copy of the previous query or current week's data query. Rename the copied query to LastWeekData. To get the Last Week data, we need to change the filter condition. Following is the code for the LastWeekData query filter.
cast([Presentation].[Visitors].[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)
Our query for Current Week and Last Week Data is ready. Add a new data item in both the query names it Week. Add a hard-coded value in this data item. For the CurrentWeekData query use ‘Current Week’. For the LastWeekData query use ‘Last Week’. Finally, create the last query with name Visitors. The new query will be the Union of CurrentWeekData and LastWeekData query. We will use the final Visitors query inside the Line chart and the Cross Tab. If everything is correct you will get the output as shown in the following image.
Please login to post your valuable comments.