As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name. Connect to myibi
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]how to calculate the week of the month

 Go Search Notify Tools
 [SOLVED]how to calculate the week of the month
Platinum Member
 posted April 17, 2020 09:22 AM
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.

Thanks

This message has been edited. Last edited by: Trudy,

WF8
Windows

 Posts: 117 | Registered: May 28, 2015 IP
Expert
 posted April 17, 2020 09:50 AM Hide Post
If your week in month number always starts on the first of the month, then -

month_wk/I2 = INT(day_of_month / 7) + 1;

T

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004 IP
Silver Member
 posted April 17, 2020 10:39 AM Hide Post
Hi Trudy, hope you been well.

I know in T-SQL world this works:
```declare @date_given datetime = '2020-04-17'
```

http://sqlfiddle.com/#!18/a7540/27697

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

 Posts: 45 | Registered: October 07, 2015 IP
Platinum Member
 posted April 17, 2020 11:39 AM Hide Post
Tony,

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.

WF8
Windows

 Posts: 117 | Registered: May 28, 2015 IP
Expert
 posted April 17, 2020 12:15 PM Hide Post
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.

Good luck

T

This message has been edited. Last edited by: Tony A,

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004 IP
Expert
 posted April 17, 2020 01:03 PM Hide Post
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
-* 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```

T

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004 IP
Expert
 posted April 18, 2020 12:57 PM Hide Post
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
...
```

 In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206

 Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005 IP
Platinum Member
 posted April 20, 2020 12:36 PM Hide Post
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 WEEK2/I2 = EDIT(SUBSTR(10, ISODATE2, 7, 9, 2, 'A2'));
COMPUTE monthdate/I11 = DPART(AddDateYYMD, 'WEEK', 'I11');
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;
```

WF8
Windows

 Posts: 117 | Registered: May 28, 2015 IP
Platinum Member
 posted April 20, 2020 12:49 PM Hide Post
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 WEEK2/I2 = EDIT(SUBSTR(10, ISODATE2, 7, 9, 2, 'A2'));
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;
```

WF8
Windows

 Posts: 117 | Registered: May 28, 2015 IP
Platinum Member
 posted April 21, 2020 09:44 AM Hide Post
Trudy, I tried your code. It does not seem to work for the month of January. Am I missing something?

WebFOCUS 8.2.06

 Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010 IP
Expert
 posted April 21, 2020 10:51 AM Hide Post
quote:
Am I missing something?

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;`

T

 In FOCUSsince 1986 WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2 WebFOCUS App Studio 8.2.06 standalone on Windows 10

 Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004 IP
Platinum Member
 posted April 21, 2020 01:55 PM Hide Post
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  ```

WebFOCUS 8.2.06

 Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010 IP