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] Calculate How many days monday-saturday in a month

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Calculate How many days monday-saturday in a month
 Login/Join
 
Platinum Member
posted
Hi all :
I need to calculate week days (Mon-Sat) in a given month.. I need to do this is in a Define.. I have a YearMonth field for that I need toi find out weekdays (mon-sat) OR find how many Sundays in that month and then subtract # of Sundays from the Last Day of the Month .
I looked around and found the following code, does not exactly gives me the answer I need, anyone dealt with this issue, please let me know
Thanks

 
DEFINE FUNCTION SCNDSAT/A8YYMD (SEEDDATE/A8YYMD)
 SEEDYYMD/YYMD = SEEDDATE ;
 CALC1/YYMD = DATEMOV(SEEDYYMD, 'BOM') ;
 DOW/W = CALC1 ;
FIX/I1 = IF DOW EQ 7 THEN 7 ELSE 0 ;
 CALC2/YYMD = CALC1  + (6 - DOW) + 7 + FIX ;
 SCNDSAT/A8YYMD = CALC2;
END

-RUN

-SET &TESTDATE = '20141001' ;
-SET &RES = SCNDSAT(&TESTDATE) ;
-SET &DAYOFWK = DOWKL(&RES, 'A12');
-TYPE &TESTDATE - &RES
-TYPE DAY OF WEEK &DAYOFWK 

 

This message has been edited. Last edited by: <Kathryn Henning>,


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Report This Post
Virtuoso
posted Hide Post
Here something that I have to give you a starting point :
DEFINE FILE BUSINESS_CALENDAR
WDAY   /A3 = DOWK(CAL_DTE, WDAY);
NB_SUN /D2 = 1;
END
TABLE FILE BUSINESS_CALENDAR
SUM NB_SUN
BY ACC_MTH_NO

WHERE ACC_YR       EQ 2014;
WHERE ACC_MTH_NO   GE 01;
WHERE ACC_MTH_NO   LE 12;

WHERE WDAY EQ 'SUN';

ON ACC_MTH_NO SUBFOOT

ON TABLE COLUMN-TOTAL AS 'Total'
END


Resulting in
ACC_MTH_NO NB_SUN 
1          4 
2          4 
3          5 
4          4 
5          4 
6          5 
7          4 
8          5 
9          4 
10         4 
11         5 
12         4 
Total     52 

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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
If you are unconcerned with excluding holidays, here's the approach I would take:

Sunday_before/YYMD= [Sunday at the beginning of the (Sunday through Saturday) calendar week of the start date.]*
Sunday_after/YYMD = [Sunday following the end of the calendar week of the end date.]*
gross_count/I4=6*INT((Sunday_after - Sunday_before)/7);
adjust_before/I1=[number of Mon-Sat days preceeding start date within its calendar week}*
adjust_after/I1= [number of Mon-Sat days following end date within its calendar week]*
net_count/I4 = gross_count - (adjust_befpore + adjust_after);


Once the start and end dates are converted to YYMD ("smart date") format, the day-of-week of each can be obtains simply by casting from format YYMD to format W, and the number-of-days adjustments for calculating the starred items can be computed using DECODE.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
Thank you Martin , I tried your method, but I get only one Count Sunday.. My reporting file has one row
Year1, Year2, Year3,. Mon1, Mon2, Mon3...

 
-* Find Sundays in a Month
GL_DTE3X/A6=EDIT(GLRE_ACT_YR3,'$$99')|GLRE_ACCT_MTH3|'01';
GL_DTE3Y/I6=EDIT(GL_DTE3X);
GL_DTE3/I6YMD=GL_DTE3Y;
WDAY   /A3 = DOWK(GL_DTE3, WDAY);
NB_SUN /D2 = 1;
END
-RUN
TABLE FILE ITREND3
SUM GL_DTE3X GLRE_ACT_YR3 GLRE_ACCT_MTH3
   GL_DTE3Y GL_DTE3 WDAY
 NB_SUN
BY GL_DTE3
-*WHERE RPT_YR       EQ 2014;
-*WHERE RPT_MO       GE 01;
-*WHERE RPT_MO      LE 12;
WHERE WDAY EQ 'SUN';
ON GL_DTE3 SUBFOOT
" "
ON TABLE COLUMN-TOTAL AS 'Total'
END
-RUN
-EXIT
Result
I printed all to see whats going on
GL_DTE3 GL_DTE3X GLRE_ACT_YR3 GLRE_ACCT_MTH3 GL_DTE3Y GL_DTE3 WDAY NB_SUN 
12/12/01 121201 2012 12 121201 12/12/01 SUN 1 
 


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Report This Post
Virtuoso
posted Hide Post
You need to process the count by month, if you count it by a date it will only gives you 1 Sunday.

Try first to do
TABLE FILE ITREND3
PRINT GL_DTE3X
      GLRE_ACT_YR3
      GLRE_ACCT_MTH3
      GL_DTE3Y
      WDAY
BY GL_DTE3
END


Just to validate that your DEFINES are good.

It certainly not good because appending 01 as the day will generates every months with only one day. So you won't have all the days of the month and you need all of them to count number of Sunday.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Yes I already did print the DEFINES and they are working..before I posted
Since I have only one row where months are in 12 buckets Mon1. Mon2...Mon12 Yr1, Yr2. Yr3..Yr12

In your sample above what is defined CAL_DTE, DAY_X as?
my source flds are Yr and Mon
Thanks

Thanks


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Report This Post
Virtuoso
posted Hide Post
CAL_DTE is a date field from the source table.

To count how many Sunday you have in a month, you need all days from that month! Not only a YearMonth field.

How will I know how many Sunday I have if I don't know which days falls a Sunday ?
For what I understand, you don't so you'll have to create a "calendar" such as:

-* BELOW VARIABLE SHOULD BE ASSIGNED TO YOUR YEAR/MONTH FIELD
-SET &YEAR = 2014;
-SET &MONTH = 9;

-SET &I = 0;
-SET &NBSUNDAY = 0;

-* NOT PROCESSING FOR LEAP YEAR, MUST DO A TEST ON YEAR FOR IT
-SET &NBMTHDAY = DECODE &MONTH (1 31 2 28 3 31 4 30 5 31 6 30 7 31 8 31 9 30 10 31 11 30 12 31);
-LOOP
-SET &I = &I + 1;

-SET &DTE&I.EVAL = (&YEAR * 10000) + (&MONTH * 100) + &I;
-SET &DAY = DOWK(&DTE&I.EVAL, 'A3');
-SET &NBSUNDAY = IF &DAY EQ 'SUN' THEN &NBSUNDAY + 1 ELSE &NBSUNDAY;
-IF &I LT &NBMTHDAY THEN GOTO LOOP;

-TYPE NB_SUNDAY: &NBSUNDAY


This gives you the number of Sunday in September 2014.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
Can't this be accomplished by using the business days function with a DATEDIF()?

 
SET BUSDAYS = _MTWTFS
In a define set
Days_Without_Sunday/I6=DATEDIF(<Begin Date>,<End Date>, 'BD');

 


WebFOCUS 8.1.05
Windows-iSeries DB2, All Outputs
HTML
 
Posts: 29 | Location: Cincinnati, OH | Registered: August 23, 2011Report This Post
Gold member
posted Hide Post
Try:
 -DEFAULTS &YYM=201410                                                 
DEFINE FILE CAR                                                       
-*MYMON/YYM    : target year-month                                    
-*MYFST/YYMD   : date of 1st day of month                             
-*MYFSTWD/I1   : weekday of 1st day of month (monday=0)               
-*FSTMON/YYMD  : date of 1st monday of month                          
-*MYNEXT/YYM   : year-month after target month                        
-*MYNXFST/YYMD : date of 1st day of next month                        
-*MYNFSTWD/I1  : weekday of 1st day of next month                     
-*LSTMON/YYMD  : date of last monday of month                         
-*WEEKS/I1     : number of full weeks between 1st and last mondays    
-*LDAYS/I1     : number of weekdays in week before 1st monday         
-*FDAYS/I1     : number of weekdays in week after last monday         
CTR/I3 WITH BODYTYPE = CTR+1 ;                                        
MYMON/YYM WITH BODYTYPE = '&YYM' ;                                    
MYMON/YYM = MYMON-12+CTR ;                                            
MYFST/YYMD = MYMON ;                                                  
MYFSTWD/I1 = MYFST - ((INT(MYFST/7))*7) ;                             
FSTMON/YYMD = MYFST + DECODE MYFSTWD(0 0 1 6 2 5 3 4 4 3 5 2 6 1) ;   
MYNEXT/YYM = MYMON + 1 ;                                              
MYNXFST/YYMD = MYNEXT ;                                               
MYNFSTWD/I1 = MYNXFST - ((INT(MYNXFST/7))*7) ;                        
LSTMON/YYMD = MYNXFST - DECODE MYNFSTWD(0 7 1 1 2 2 3 3 4 4 5 5 6 6) ;
WEEKS/I1 = (LSTMON-FSTMON)/7 ;                                        
LDAYS/I1 = MYNXFST - LSTMON ;                                         
LDAYS/I1 = IF LDAYS GE 5 THEN 5 ELSE LDAYS ;                          
FDAYS/I1 = (FSTMON - MYFST)-2 ;                                       
FDAYS/I1 = IF FDAYS LT 0 THEN 0 ELSE FDAYS ;                          
DAYSINMON/I2 = WEEKS*5 + FDAYS + LDAYS ;                              
END                                                                   
TABLE FILE CAR                                                        
PRINT MYMON DAYSINMON                                                 
END                                                                   
-RUN                                                                  
 


It seems to work for this year and the first few months of next...


IBI Development
 
Posts: 61 | Registered: November 15, 2005Report This Post
Virtuoso
posted Hide Post
That's nice Todd,
It is important to get the correct end date:
SET BUSDAYS = _MTWTFS

DEFINE FUNCTION WD_MONTH (year/I4,month/I2)
this_iyym/I6YYM = (year*100)+month;
bom_yymd/YYMD = this_iyym;
WD_MONTH/I2 = DATEDIF(bom_yymd,DATEMOV(bom_yymd,'EOM')+1,'BD');
END

seems to work okay when year and month are sent as integers.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Thank you all.. All the above worked .. but I picked Alan's approach as it takes less code.. I have 12 of these buckets, I have to do it for.. See below

Thanks everyone

here is the code..
 
SET BUSDAYS = _MTWTFS
..
..
-* Find mon-sat days in a month - (6 days a week)
DEFINE FUNCTION WD_MONTH (year/I4,month/I2)
this_iyym/I6YYM = (year*100)+month;
bom_yymd/YYMD = this_iyym;
WD_MONTH/I2 = DATEDIF(bom_yymd,DATEMOV(bom_yymd,'EOM')+1,'BD');
END
-RUN
..
..
DEFINE FILE TREND3
-* Find weekdays mon-sat by month
GLRE_ACTYR1/I4 =EDIT(GLRE_ACT_YR1); 
GLRE_ACTMTH1/I2=EDIT(GLRE_ACCT_MTH1);
WEEKDAYS1/I2 = WD_MONTH(GLRE_ACTYR1,GLRE_ACTMTH1);
-*
GLRE_ACTYR2/I4 =EDIT(GLRE_ACT_YR2); 
GLRE_ACTMTH2/I2=EDIT(GLRE_ACCT_MTH2);
WEEKDAYS2/I2= WD_MONTH(GLRE_ACTYR2,GLRE_ACTMTH2);
-*
GLRE_ACTYR3/I4 =EDIT(GLRE_ACT_YR3); 
GLRE_ACTMTH3/I2=EDIT(GLRE_ACCT_MTH3);
WEEKDAYS3/I2 = WD_MONTH(GLRE_ACTYR3,GLRE_ACTMTH3);
..
..
END
-RUN
TF..

Results
YR1 MTH1 WDAYS1 YR2 MTH2 WDAYS2 YR3  MTH3 WDAYS3 
2012 10 27      2012 11 26      2012 12 26 
 


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Report This Post
Virtuoso
posted Hide Post
My suggestion above (for a closed form definition of days, excluding Sundays, in a given month), fleshed out.

TABLE FILE CAR
LIST MODEL
IF RECORDLIMIT EQ 14
ON TABLE HOLD
END
DEFINE FILE HOLD
AMONTH/A6YYM = DECODE LIST (
1 '201409' 2 '201410' 3 '201411' 4 '201412' 5 '201501' 6 '201502' 7 '201503'
8 '201504' 9 '201505' 10 '201506' 11 '201507' 12 '201508' 13 '201509' 14 '201510'
ELSE '');
MONTH/YYM=AMONTH;
FST/YYMDWt=MONTH;
NXMONTH/YYM=MONTH+1;
NXT/YYMDWt=NXMONTH;
LST/YYMDWt=NXT-1;
WFST/W=FST;
WLST/W=LST;
IFST/I1=IMOD(WFST,7,'I1');
ILST/I1=IMOD(WLST,7,'I1');
SUN1/YYMDW=FST-IFST;
SUN2/YYMDW=LST+7-ILST;
DAYS/I3=INT((SUN2-SUN1)/7)*6 - ( MAX(0,IFST-1) + (6-ILST) );

END
TABLE FILE HOLD
PRINT MONTH FST LST IFST ILST WEEKDAYS
BY LIST
END

MONTH is the month, in smart-date format

FST is first date in the month. (Casting a YYM as YYMD yields the first of the month)
NXMONTH is the following month
NXT is its start date
LST is the last date of the given month

WFST is the day-of-week of FST. (WF convention for the internal value is Mon=1 thru Sun=7)
WLST - ditto for LST
IFST is weekday index (0=Sun to 6=Sat) of FST
ILST - ditto for LST

SUN1 is date of the last Sunday on or before the 1st of the given month
SUN2 is date of first Sunday following the end of the given month
DAYS is the desired result.

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
If the values are Alpha, change the function to accept those, less code needed:
SET BUSDAYS = _MTWTFS

DEFINE FUNCTION WD_MONTH (year/A4,month/A2)
this_iyym/I6YYM = (EDIT(year)*100)+EDIT(month);
bom_yymd/YYMD = this_iyym;
WD_MONTH/I2 = DATEDIF(bom_yymd,DATEMOV(bom_yymd,'EOM')+1,'BD');
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Thanks Jack, Alan and others..
Issue resolved.


Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 )
SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2
WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
 
Posts: 134 | Location: USA | Registered: August 21, 2008Report 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] Calculate How many days monday-saturday in a month

Copyright © 1996-2020 Information Builders