Cognos Report studio provides many functions for determining Day of Week, Day of Month, First of month, Last of month, but there is no function to determine the week of the month.
Based on some Binging from internet, i came up with logic to identify the week number.
Replace Audit date with whatever date like Report Date. Some months have 6 weeks, like August 2009, where 1st and 2nd of august is Saturday and Sunday. Following logic is based on Monday as the start of the week.
CASE
WHEN (Cast(substring([Audit Date],9,2),int) BETWEEN
(8-_Day_of_week(_first_of_month([Audit Date]),1)+28) and
(8-_Day_of_week(_first_of_month([Audit Date]),1)+34) ) THEN 6
WHEN (Cast(substring([Audit Date],9,2),int) BETWEEN
(8-_Day_of_week(_first_of_month([Audit Date]),1)+21) and
(8-_Day_of_week(_first_of_month([Audit Date]),1)+27) ) THEN 5
WHEN (Cast(substring([Audit Date],9,2),int) BETWEEN
(8-_Day_of_week(_first_of_month([Audit Date]),1)+14) and
(8-_Day_of_week(_first_of_month([Audit Date]),1)+20) ) THEN 4
WHEN (Cast(substring([Audit Date],9,2),int) BETWEEN
(8-_Day_of_week(_first_of_month([Audit Date]),1)+7) and
(8-_Day_of_week(_first_of_month([Audit Date]),1)+13) ) THEN 3
WHEN (Cast(substring([Audit Date],9,2),int) BETWEEN
(8-_Day_of_week(_first_of_month([Audit Date]),1)+1) and
(8-_Day_of_week(_first_of_month([Audit Date]),1)+6) )THEN 2
WHEN (Cast(substring([Audit Date],9,2),int) <
(8-_Day_of_week(_first_of_month([Audit Date]),1))) THEN 1
ELSE 0
END
Subscribe