Number of specific weekday between two dates.

[KEKeogh]

Anyone know if Cognos 8.2 is able to count the number of Fridays between two dates?

Thanks
Kathie

[KEKeogh]

Excuse me it's 8.3.

To clarify:
I need to count the number of Mondays, Tuesdays, Wednesdays, etc. when the column I call "SLC_Day" equals that specific day.

[Arsenal]

How is the specific date coming in? Is it a prompt or just the current date? Does a variation of _days_between(current_date,?dateprompt?) work for you?

[Lynn]

If you have a well designed date dimension that would be the easiest. A good one will typically indicate the day of the week so you could just do a count between the desired dates.

If you don't have a date dimension, you can do the math. This is a little mind-bending and someone better in math might know an easier way...but this is how I see it.

Compute the number of days between the two dates and divide by 7 to get the number of weeks. The number of mondays, tuesdays, etc. will be EITHER the ceiling or the floor of this number.

For example, if the start date is a Friday and the end date is a Wednesday and the weeks computation is 20.xxxx then there are 20 Thursdays (floor) and the count for every other day of the week is 21 (ceiling). In this case Thursday is the "gap" between the start day and end day. The assumption is that start and end day are inclusive.

As another example, a start date that happens to be a Thursday and an end date that happens to be a Friday means that the ceiling is used for Thursday and Friday while the floor is used for all the other week days because all those other days are the "gap".

To try it out, create 7 query items (one for each day of the week) as below. I've shown Mon, Tue, and Wed so you can see the pattern. Continue on in pattern for Thu, etc. This is based on your start day such that the start date day of week is 1, the day after that is 2, etc.

Code Select Expand

Mon:
case _day_of_week ([StartDate],1)
when 1 then 1
when 2 then 7
when 3 then 6
when 4 then 5
when 5 then 4
when 6 then 3
when 7 then 2
end

Tue:
case _day_of_week ([StartDate],1)
when 1 then 2
when 2 then 1
when 3 then 7
when 4 then 6
when 5 then 5
when 6 then 4
when 7 then 3
end

Wed:
case _day_of_week ([StartDate],1)
when 1 then 3
when 2 then 2
when 3 then 1
when 4 then 7
when 5 then 6
when 6 then 5
when 7 then 4
end

Next, create a query item to align the end date within the sequence established:

Code Select Expand

DayOfWeekEnd:
case _day_of_week ([EndDate],1)
when 1 then [Monday]
when 2 then [Tuesday]
when 3 then [Wednesday]
when 4 then [Thursday]
when 5 then [Friday]
when 6 then [Saturday]
when 7 then [Sunday]
end

Now you can detect the gap and decide whether to use floor or ceiling. Create another 7 query items to compare the elements:

Code Select Expand

NumberOfMondays:
if ( [Mon] <= [DayOfWeekEnd] )
then ( ceiling ( _days_between([EndDate],[StartDate]) / 7 ) )
else ( floor ( _days_between([EndDate],[StartDate]) / 7 ) )

NumberOfTuesdays:
if ( [Tue] <= [DayOfWeekEnd] )
then ( ceiling ( _days_between([EndDate],[StartDate]) / 7 ) )
else ( floor ( _days_between([EndDate],[StartDate]) / 7 ) )

NumberOfWednesdays:
if ( [Wed] <= [DayOfWeekEnd] )
then ( ceiling ( _days_between([EndDate],[StartDate]) / 7 ) )
else ( floor ( _days_between([EndDate],[StartDate]) / 7 ) )

And there you have it. Easy as pie :)

Note that when start and end date are the same (e.g., Thursday to Thursday) it could be considered ceiling+1 rather than just ceiling. I don't have logic reflected for that scenario.