Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: KEKeogh on 07 Oct 2010 02:56:07 PM

Title: Count the specific weekdays in a year
Post by: KEKeogh on 07 Oct 2010 02:56:07 PM
I am trying to count the number of a specific weekday for the year 2010.

For example I want it to tell there are 52 Mondays in between 1-1-2010 and 12-31-2010.  I need to do it for each day of the week.

I know I can do it in Excel but can't find a Cognos 8.3 equivalent.

Anyone have any clues?

thanks
Kathie
Title: Re: Count the specific weekdays in a year
Post by: cognostechie on 07 Oct 2010 04:33:20 PM
Not sure if this is the best way to do it but try this:

This would require you to have a table that has one row for every date from 1/1/2010 to 12/31/2010.
Let's say the name of the Date field is 'Date'

Create a calculated column and use this expression:

If (_day_of_week([Query Subject Name].[Date],1) = 1) then ('Monday')
Else If (_day_of_week([Query Subject Name].[Date],1) = 2) then ('Tuesday')
Else If (_day_of_week([Query Subject Name].[Date],1) = 3) then ('Wednesday')
Else If (_day_of_week([Query Subject Name].[Date],1) = 4) then ('Thursday')
Else If (_day_of_week([Query Subject Name].[Date],1) = 5) then ('Friday')
Else If (_day_of_week([Query Subject Name].[Date],1) = 6) then ('Saturday')
else ('Sunday')

Let's say the name of the above calculation is 'Day of Week'

Create another calculated column and use this:

If ([Day of Week] = 'Monday') then (1) else (0)

Sum this 2nd column and it would give you the number of Mondays during the year.
Title: Re: Count the specific weekdays in a year
Post by: KEKeogh on 08 Oct 2010 10:39:12 AM
And of course we have no table with all the dates in it.  I'll try creating one and see if it works.
Title: Re: Count the specific weekdays in a year
Post by: rockytopmark on 08 Oct 2010 10:55:58 AM
Good Idea... the Date Dimension is often referred to as the single most important dimension in a Data Warehouse, so it is vital to have one.
Title: Re: Count the specific weekdays in a year
Post by: pmcgraw on 08 Oct 2010 07:00:21 PM
if you are using an oracle database, you can use the following queries

First, calculate the number of days between two dates

(_days_between ([Date Received],[Date Reported])+1)

then you can calculate the number of weekend days


floor((_days_between([Date Received],[Date Reported])+_day_of_week([Date Reported],1))/7)*2 -if(_day_of_week([Date Reported],1)=7) then (1) else(0) + if (_day_of_week([Date Received],1)=6) then (1) else (0)

the number of weekdays is the difference between these two queries.

Please note, that any public holiday will not be excluded
Text only | Text with Images