Does anyone know how to calculate the week of the month? I can get the week of the year but need to know the week of each month. The values would be between 1-5.
ThanksThis message has been edited. Last edited by: Trudy,
If your week in month number always starts on the first of the month, then -
month_wk/I2 = INT(day_of_month / 7) + 1;
Hi Trudy, hope you been well.
I know in T-SQL world this works:
declare @date_given datetime = '2020-04-17' select (DATEPART(wk,@date_given)-DATEPART(wk,dateadd(d,1-day(@date_given),@date_given)))+1
For FOCUS, I think it would be in the neighborhood of
DEFINE FILE CAR MONTH_DATE/I2 = (DPART(&YYMD, 'WEEK', 'I11')-DPART(DATEADD(&YYMD, 'D', +1), 'WEEK', 'I11'))+1;
Sorry haven't had enough caffine to finish that one out, but maybe it points you in the right direciton.
WF Version 8105
Not sure I understand how your post works. If the 1st day of the month falls on a Friday then the 4th would be on the Monday of the following week so the 1st would be week 1 and the 4th should be week 2 but with your calculation wouldn't they both still be week 1? Maybe I am not understanding what day_of_month is in your example.
Precisely why I stated "If your week in month number always starts on the first of the month".
From your reply, it is now clear that you want your weeks to run from Monday to Sunday - you didn't state that in your question, so I had to guess!
Perhaps obtain the week number within the year from the 1st of the month and take this value away from the week number of the other days within the month?
Use DATEMOV(date in YYMD, 'BOM') to get the begging of the month for the date in your data.
Use DTPART(date in YYMD, WEEK) to get a 2 digit week number for both the beginning of the month and the date in your data.
Finally work out which week number a date falls within (second date minus week of first of month).
Remember that dates may belong to a week from the previous year.
Check out using ISO dates depending upon your requirement also the setting of WEEKFIRST.
TThis message has been edited. Last edited by: Tony A,
As an example and tester, use the following code to create a temporary table for every date in the current year -
FILEDEF DATEMAS DISK DATERNG.MAS -RUN -WRITE DATEMAS FILE=DATERNG,SUFFIX=XFOC -WRITE DATEMAS SEGNAME=SEG1 -WRITE DATEMAS FIELD=DATE_KEY, ,DMYY ,DMYY , $ -RUN -* Now create it CREATE FILE DATERNG -* and add all possible dates within a month MODIFY FILE DATERNG FREEFORM DATE_KEY.A8. LOG FORMAT MSG OFF LOG TRANS MSG OFF LOG INVALID MSG OFF -* Any invalid dates will be rejected. -DEFAULT &Year = 2020 -SET &Year = EDIT(&YYMD,'9999$'); DATA -* This repeat loop will ensure at least a whole year is input, -SET &Month = 1; -SET &Day = 1; -REPEAT :Loop0 12 TIMES; -SET &Month = FPRINT(&Month, 'I2L', 'A2'); -REPEAT :Loop1 31 TIMES; -SET &Date = FPRINT(&Day, 'I2L', 'A2'); &Date&Month&Year -SET &Day = &Day + 1; -:Loop1 -SET &Day = 1; -SET &Month = &Month + 1; -:Loop0 END -RUN
Then use the following code to test the week in month calculation -
-* Set the first day of week to Mondays SET WEEKFIRST = 2 TABLE FILE DATERNG SUM COMPUTE FSTOFMNTH/YYMD = DATEMOV(DATE_KEY, 'BOM'); NOPRINT COMPUTE FSTWEEK/I2 = DTPART(FSTOFMNTH, WEEK); NOPRINT COMPUTE CURWEEK/I2 = DTPART(DATE_KEY, WEEK); NOPRINT COMPUTE DAYOFWK/A3 = DOWK(DATECVT(DATE_KEY, 'YYMD', 'I8YYMD'), 'A3'); COMPUTE WEEKINMNTH/I2 = IF FSTWEEK GE 52 THEN CURWEEK - ((CURWEEK GE 52) * CURWEEK) + 1 ELSE CURWEEK - FSTWEEK + 1; BY DATE END -RUN
Here's some code that I created and used for my Financial Calendar back in 2016... It's just the basis and should still work today.
SET WEEKFIRST = 1 SET WEEKFIRST = ISO2 ... DEFINE FILE InputFile RPTDATE/A8MDYY = TRANSDATE ; ... -* Define MONTH... -* The day of week (DOW) gives us the column number but this is Sunday thru Saturday -* and we require Monday thru Sunday DOWX/W = TRANSDATE; DOWNMBR/W = IF DOWX = 7 THEN 1 ELSE DOWX + 1 ; DAYNAME/A9 = DECODE DOW (1 Sunday 2 Monday 3 Tuesday 4 Wednesday 5 Thursday 6 Friday 7 Saturday) ; ... END TABLE FILE InputFile SUM FIELD1 FIELD2 ... BY MONTH ACROSS DOWNMBR NOPORINT ACROSS DOWNAME AS 'Whatever' END ...
Thanks to everyone. I used the following code to get the two weeks like Tony suggested and then subtracted them to get the WEEKOFMONTH. Works beautifully.
COMPUTE DT2/A20 = FPRINT(SFRSTCR_ADD_DATE, 'HYYMDS', 'A20'); COMPUTE MONTH_NUM2/A2 = SUBSTR(20, DT2, 6, 7, 2, 'A2'); COMPUTE MONTH2/A11 = DECODE MONTH_NUM2( '01' 'January' '02' 'Febuary' '03' 'March' '04' 'April' '05' 'May' '06' 'June' '07' 'July' '08' 'August' '09' 'September' '10' 'October' '11' 'November' ELSE 'December' ); NOPRINT COMPUTE ISODATE2/A10 = HYYWD(SFRSTCR_ADD_DATE, 'A10'); COMPUTE WEEK2/I2 = EDIT(SUBSTR(10, ISODATE2, 7, 9, 2, 'A2')); COMPUTE monthdate/I11 = DPART(AddDateYYMD, 'WEEK', 'I11'); COMPUTE BOMDT/HYYMD = DATEMOV(SFRSTCR.SFRSTCR.AddDateYYMD, 'BOM'); COMPUTE DT1/A20 = FPRINT(BOMDT, 'HYYMDS', 'A20'); COMPUTE MONTH_NUM1/A2 = SUBSTR(20, DT1, 6, 7, 2, 'A2'); COMPUTE MONTH1/A11 = DECODE MONTH_NUM1('01' 'January' '02' 'Febuary' '03' 'March' '04' 'April' '05' 'May' '06' 'June' '07' 'July' '08' 'August' '09' 'September' '10' 'October' '11' 'November' ELSE 'December'); NOPRINT COMPUTE ISODATE1/A10 = HYYWD(BOMDT, 'A10'); COMPUTE WEEK1/I2 = EDIT(SUBSTR(10, ISODATE1, 7, 9, 2, 'A2')); COMPUTE WEEKOFMONTH/I1=WEEK2-WEEK1+1;
Sorry folks realized I posted extra field calculations that I need for my report that aren't necessary for the week of month calculation so below is the cleaner code.
COMPUTE DT2/A20 = FPRINT(SFRSTCR_ADD_DATE, 'HYYMDS', 'A20'); COMPUTE ISODATE2/A10 = HYYWD(SFRSTCR_ADD_DATE, 'A10'); COMPUTE WEEK2/I2 = EDIT(SUBSTR(10, ISODATE2, 7, 9, 2, 'A2')); COMPUTE BOMDT/HYYMD = DATEMOV(SFRSTCR.SFRSTCR.AddDateYYMD, 'BOM'); COMPUTE DT1/A20 = FPRINT(BOMDT, 'HYYMDS', 'A20'); COMPUTE ISODATE1/A10 = HYYWD(BOMDT, 'A10'); COMPUTE WEEK1/I2 = EDIT(SUBSTR(10, ISODATE1, 7, 9, 2, 'A2')); COMPUTE WEEKOFMONTH/I1=WEEK2-WEEK1+1;
Trudy, I tried your code. It does not seem to work for the month of January. Am I missing something?
Win10 / IE11
AHTML EXL2K PDF
DB, No, you're not missing anything. There is no allowance for the first week of the year belonging to the last week of the previous year. I mentioned that this needs to be allowed for in my initial post.
Using the following in Trudy's code will sort that out -
COMPUTE WEEKOFMNTH/I2 = IF WEEK1 GE 52 THEN WEEK2 - ((WEEK2 GE 52) * WEEK2) + 1 ELSE WEEK2 - WEEK1 + 1;
Perhaps you want to have the weeks run from Saturday to Friday to avoid having a weekday occur in a week number 6. I think the following code should work. It allows you to specify which day of the week is the start of a week. The last three COMPUTE statements accomplish the week of month calculation. The first sections of the code generate a file with dates for testing.
/* Can set the day that represents the first day of the week. */ /* Week Start Day 1=Mon 2=Tue 3=Wed 4=Thu 5=Fri 6=Sat 7=Sun */ -SET &Weekstart = 6; -SET &StartDate = &DATEYY | '0101'; -SET &EndDate = &DATEYY | '1231'; -SET &Date = &StartDate; -SET &i = 1; SQL CREATE TABLE calendar ( KEY INTEGER, DATEYYMD DATE ); END MODIFY FILE calendar MATCH KEY ON MATCH REJECT ON NOMATCH INCLUDE DATA -REPEAT ENDREPEAT WHILE &EndDate GE &Date ; &i,'&Date',$ -SET &i = &i + 1; -SET &Date = AYMD(&Date, 1, 'I8YYMD'); -ENDREPEAT END DEFINE FILE calendar DATEMYY/MYY = DATEYYMD; END TABLE FILE calendar PRINT COMPUTE DATEW/W = DATEYYMD; COMPUTE DATEWtr/Wtr = DATEYYMD; COMPUTE DATED/D = DATEYYMD; NOPRINT COMPUTE DATEBOMW/W = DATEMOV(DATEYYMD, 'BOM'); NOPRINT COMPUTE Week/I2 = ( DATED - 1 + MOD(DATEBOMW - &Weekstart + 7, 7) ) / 7 + 1 ; BY DATEMYY NOPRINT BY DATEYYMD ON DATEMYY PAGE-BREAK END
Win10 / IE11
AHTML EXL2K PDF
|Powered by Social Strata|