Focal Point Banner


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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]how to calculate the week of the month

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]how to calculate the week of the month
 Login/Join
 
Platinum Member
posted
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, 2015Report This Post
Expert
posted 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 FOCUS
since 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, 2004Report This Post
Silver Member
posted Hide Post
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


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, 2015Report This Post
Platinum Member
posted 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, 2015Report This Post
Expert
posted 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 FOCUS
since 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, 2004Report This Post
Expert
posted 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
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


T



In FOCUS
since 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, 2004Report This Post
Expert
posted 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, 2005Report This Post
Platinum Member
posted 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 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;
  


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Platinum Member
posted 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 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;
  


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Platinum Member
posted 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, 2010Report This Post
Expert
posted 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 FOCUS
since 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, 2004Report This Post
Platinum Member
posted 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, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]how to calculate the week of the month

Copyright © 1996-2020 Information Builders