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
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.
And of course we have no table with all the dates in it. I'll try creating one and see if it works.
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.
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