Just for the sake of ease of reading, you could alter your statement to be:
CASE
when (Day of Month]) between 1 and 7 then 1
when (Day of Month]) between 8 and 14 then 2
when (Day of Month]) between 15 and 21 then 3
when (Day of Month]) between 22 and 28 then 4
when (Day of Month]) between 29 and 31 then 5
else 999
end
------------------------------
Chris Turner
------------------------------
Original Message:
Sent: Tue June 09, 2020 01:00 PM
From: Elizabeth Griffin
Subject: How to calculate week of a month (instead of year)
I added a data item in my query using a case statement based off day of month
CASE ([Day of Month])
WHEN (1) THEN (1)
WHEN (2) THEN (1)
WHEN (3) THEN (1)
WHEN (4) THEN (1)
WHEN (5) THEN (1)
WHEN (6) THEN (1)
WHEN (7) THEN (1)
WHEN (8) THEN (2)
WHEN (9) THEN (2)
WHEN (10) THEN (2)
WHEN (11) THEN (2)
WHEN (12) THEN (2)
WHEN (13) THEN (2)
WHEN (14) THEN (2)
WHEN (15) THEN (3)
WHEN (16) THEN (3)
WHEN (17) THEN (3)
WHEN (18) THEN (3)
WHEN (19) THEN (3)
WHEN (20) THEN (3)
WHEN (21) THEN (3)
WHEN (22) THEN (4)
WHEN (23) THEN (4)
WHEN (24) THEN (4)
WHEN (25) THEN (4)
WHEN (26) THEN (4)
WHEN (27) THEN (4)
WHEN (28) THEN (4)
WHEN (29) THEN (5)
WHEN (30) THEN (5)
WHEN (31) THEN (5)
END
If you were looking for the 52 weeks, you could write a long case statement based of month and day. Not ideal but it works.
Another idea is to make a new data source with a data module using an excel or text file as a lookup table and link that to your current data source.
------------------------------
Elizabeth Griffin
Original Message:
Sent: Fri June 05, 2020 02:47 PM
From: MELODY ALBERTI
Subject: How to calculate week of a month (instead of year)
Hi everyone, as you know, the "week_of_year" function shows the week number of the year. What I'd like to show is the week of the month (1, 2, 3, 4 or 5 depending on the month). Has anyone tried to do this in Cognos? We're running CA 11.1 R6. I would appreciate any help with this type of calculation. Thanks so much! Regards, Melody
------------------------------
MELODY ALBERTI
------------------------------
#CognosAnalyticswithWatson