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] Find First Sunday or Last Saturday of a month.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Solved] Find First Sunday or Last Saturday of a month.
 Login/Join
 
peechy23
Member
posted
I am trying to calcuate the beginning and ending dates of my fiscal year for the next 60 years. This has presented certain challenges for me because our Fiscal Year year begins with the first week in the accounting month of February and ends after the last week in the next January's accounting month.

I have seen the formulas for getting the begining and ending of the month but I was wondering if there was an easy way to get the last saturday of January and then the first Sunday in February.

WebFOCUS 7.6
Windows
Output: Excel, HTML and PDF

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


WebFOCUS 8.09
Windows, All Outputs
 
Posts: 22 | Registered: August 16, 2010Report This Post
j.gross
Virtuoso
posted Hide Post
My understanding is that your fiscal years end on the last Saturday of calendar January. The following code calculates January 31, determines its day of week, and adjusts the date accordingly to the last Saturday.
DEFINE FILE CAR
 SEQ/I4 WITH MODEL = SEQ+1;
 YEAR/YY = 2000 + SEQ;
 JAN01/WMDYY=YEAR;
 JAN31/WMDYY=JAN01+30;
 WD31/WTR=JAN31;
 NWD31/I1=WD31;
 NADJ/I1=IMOD(NWD31+1,7,'I1');
FYEND/WMDYY=JAN31-NADJ;
END
-RUN
TABLEF FILE CAR
PRINT
 YEAR
 JAN31
 NWD31
 NADJ
 FYEND
END

Output:

YEAR JAN31           NWD31 NADJ FYEND          
2001 WED, 01/31/2001 3     4    SAT, 01/27/2001
2002 THU, 01/31/2002 4     5    SAT, 01/26/2002
2003 FRI, 01/31/2003 5     6    SAT, 01/25/2003
2004 SAT, 01/31/2004 6     0    SAT, 01/31/2004
2005 MON, 01/31/2005 1     2    SAT, 01/29/2005
2006 TUE, 01/31/2006 2     3    SAT, 01/28/2006
2007 WED, 01/31/2007 3     4    SAT, 01/27/2007
2008 THU, 01/31/2008 4     5    SAT, 01/26/2008
2009 SAT, 01/31/2009 6     0    SAT, 01/31/2009
2010 SUN, 01/31/2010 7     1    SAT, 01/30/2010
2011 MON, 01/31/2011 1     2    SAT, 01/29/2011
2012 TUE, 01/31/2012 2     3    SAT, 01/28/2012
2013 THU, 01/31/2013 4     5    SAT, 01/26/2013
2014 FRI, 01/31/2014 5     6    SAT, 01/25/2014
2015 SAT, 01/31/2015 6     0    SAT, 01/31/2015
2016 SUN, 01/31/2016 7     1    SAT, 01/30/2016
2017 TUE, 01/31/2017 2     3    SAT, 01/28/2017
2018 WED, 01/31/2018 3     4    SAT, 01/27/2018
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Dan Satchell
Virtuoso
posted Hide Post
A Dialogue Manager approach:

-SET &YEAR = '2012';
-SET &FIRST_FEB     = &YEAR | '0201';
-SET &DOW           = DOWK(&FIRST_FEB,'A3');
-SET &ADD_DAYS      = DECODE &DOW ('SUN' 0
-                                  'MON' 6
-                                  'TUE' 5
-                                  'WED' 4
-                                  'THU' 3
-                                  'FRI' 2
-                                  'SAT' 1 );
-SET &FIRST_SUN_FEB = AYMD(&FIRST_FEB,&ADD_DAYS,'I8YYMD');
-SET &SUBTRACT_DAYS = DECODE &DOW ('SUN' 1
-                                  'MON' 2
-                                  'TUE' 3
-                                  'WED' 4
-                                  'THU' 5
-                                  'FRI' 6
-                                  'SAT' 7 );
-SET &LAST_SAT_JAN  = AYMD(&FIRST_FEB,-&SUBTRACT_DAYS,'I8YYMD');
-*
-TYPE &FIRST_SUN_FEB
-TYPE &LAST_SAT_JAN


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
peechy23
Member
posted Hide Post
Thank you very much for the quick response on both of these. They are correct for most but I run into problem every now and then when we have a 53 week year instead of 52. I am not sure what the pattern is for that. Below is a list of what I crurrently have available out to 2018 for starting and ending dates.

Begining End FY Days in Year
2/4/2001 2/2/2002 2001 363
2/3/2002 2/1/2003 2002 363
2/2/2003 1/31/2004 2003 363
2/1/2004 1/29/2005 2004 363
1/30/2005 1/28/2006 2005 363
1/29/2006 2/3/2007 2006 370
2/4/2007 2/2/2008 2007 363
2/3/2008 1/31/2009 2008 363
2/1/2009 1/30/2010 2009 363
1/31/2010 1/29/2011 2010 363
1/30/2011 1/28/2012 2011 363
1/29/2012 2/2/2013 2012 370
2/3/2013 2/1/2014 2013 363
2/2/2014 1/31/2015 2014 363
2/1/2015 1/30/2016 2015 363
1/31/2016 1/28/2017 2016 363
1/29/2017 2/3/2018 2017 370
2/4/2018 2/2/2019 2018 363

Any thoughts on how I can incorporate this into a formula?

WebFocus 7.6
Windows
Outputs: Excel, HTML and PDF


WebFOCUS 8.09
Windows, All Outputs
 
Posts: 22 | Registered: August 16, 2010Report This Post
Crymsyn
Gold member
posted Hide Post
Looking at your latest post it seems to follow fairly closely to be the 5th week in a year.

I mainly edited j.gross's example to change from getting the FYEND to getting the start based off of the 5th week instead of the end of January.

DEFINE FILE CAR
 SEQ/I4 WITH MODEL = SEQ+1;
 YEAR_STA/YY = 2000 + SEQ;
 JAN01_STA/WMDYY=YEAR_STA;
 JAN31_STA/WMDYY=JAN01_STA+34;
 WD31_STA/WTR=JAN31_STA;
-* NWD31_STA/I1=WD31_STA;
 NADJ_STA/I1=IMOD(WD31_STA,7,'I1');
FYSTA/WMDYY=JAN31_STA-NADJ_STA;

 YEAR_END/YY = 2001 + SEQ;
 JAN01_END/WMDYY=YEAR_END;
 JAN31_END/WMDYY=JAN01_END+33;
 WD31_END/WTR=JAN31_END;
-* NWD31_END/I1=WD31_END;
 NADJ_END/I1=IMOD(WD31_END+1,7,'I1');
 FYEND/WMDYY=JAN31_END-NADJ_END;

END
-RUN
TABLEF FILE CAR
PRINT
 FYSTA
 FYEND
 YEAR_STA
END


WF: 8201, OS: Windows, Output: HTML, PDF, Excel
 
Posts: 78 | Registered: November 08, 2010Report This Post
peechy23
Member
posted Hide Post
This is absolutely great! Thank you so much. One follow up question.

What part of the code is determining how many years it runs? Ultimately I would like to be able to get the start and end dates for 60 years.


WebFOCUS 8.09
Windows, All Outputs
 
Posts: 22 | Registered: August 16, 2010Report This Post
j.gross
Virtuoso
posted Hide Post
quote:
What part of the code is determining how many years it runs?


Nothing explicit - the number of years in the output is the number of data instances (of MODEL) in the CAR file. (Note the WITH clause in the first Define.)

Choose a data source with a sufficient number of instances, and limit the output to 60 lines with

PRINT ...
WHERE RECORDLIMIT EQ 60 ;
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
peechy23
Member
posted Hide Post
This is great! Thanks everyone for your help.


WebFOCUS 8.09
Windows, All Outputs
 
Posts: 22 | Registered: August 16, 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] Find First Sunday or Last Saturday of a month.

Copyright © 1996-2020 Information Builders