If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Count the specific weekdays in a year

Started by KEKeogh, 07 Oct 2010 02:56:07 PM

Previous topic - Next topic

KEKeogh

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

cognostechie

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.

KEKeogh

And of course we have no table with all the dates in it.  I'll try creating one and see if it works.

rockytopmark

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.

pmcgraw

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