Reporting on activity by week

This is a rewrite of this post as i completely failed to understand what i’d written. The original text is left after the break for completeness.

A week is a unit of time that has to be derived, and therefore has multiple meanings. Some business will run Monday to Friday, others will run Sunday to Saturday. To calculate the week, you need to know what date the first day of that week occurred on. This is actually quite easy to derive as you will know what day of the week the current day is. If you are running a Monday – Sunday week then if today is Tuesday, it is day 2 of the week, therefore the date the week starts will be 1 day ago. If the day is Thursday, this will be day 4 of the week, therefore the date teh week starts will be 3 days ago.

To achieve this is Cognos Report Studio we will use two funcitons.

The first function is

_day_of_week ( date_expression, integer )

The first parameter is the date you want to know the day of week of. The second paramater is the day that starts the week. 1 = Monday, 2 = Tuesday, etc. So in this case we will use the value 1. This will return the day of the week.

The second function is

_add_days ( date_expression, integer_expression )
This take the first parameter, and adds to it the number of days sepcified in the second. If you prefix the second paramater with a minus (-) then it will remove that number of days.

So to work out the week number, we need to take the

Day of the week, and remove that many days from the current date. However  as the first day of the week is day 1, and not day 0. So to compensate for this we need to add another day on.

The final formula would be
_add_days(cast([Transaction Date],date),-_day_of_week([Transaction Date],1)+1)

A week is a unit of time that has to be derived, and therefore has multiple meanings. Some business will run Monday to Friday, others will run Sunday to Saturday. To calculate the week, you need to know what date the first day of that week occurred on. You can derive this from the date you are currently looking at if you know the day of the week of thecurrent date.

In Cognos report studio can use the following two functions to break data down by week.
_add_days ( date_expression, integer_expression )
_day_of_week ( date_expression, integer )

The _day_of_ week function will let you know what day of the week it is by returning a numerical value for that day, so MOnday = 1, Tuesday =2, etc. From this if it is Tuesday (returning two), the week begins on Monday which is 1 day previous. Wednesday (returning 3) is two days previous. So you want to go back by the number of days returned by the formula, but then go forward one (see fencepost or off by one error).

The final formula would be
_add_days(cast([Transaction Date],date),-_day_of_week([Transaction Date],1)+1)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s