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     [CLOSED] looping through 12 months with WebFocus

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] looping through 12 months with WebFocus
 Login/Join
 
Silver Member
posted
Hi! I'm trying to loop through 12 months (starting 12 months ago), setting each month to display the end of the month. With each iteration, I'd like to calculate the monthly backlog by determining whether the created date occurred before the end of the month and the closed date either hasn't occurred yet or occurred after the end of the month. Does anyone know how to do this without using Dialog Manager commands? I can accomplish this with Dialog Manager commands but I was told that unless I'm using a SQL pass through, all of the date manipulations should be done using DEFINE's. Thanks for any help you can provide!

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


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Virtuoso
posted Hide Post
You have to use DEFINE's when the values are tied to your data fields. In your case, it appears they are not. You just want the last 12 end-of-month dates from today (as determined by &YYMD or something similar.)

You can still calculate the end of month date for the last 12 months using dialogue manager and then compare your fields' values to those & vars.

So calculate in a loop the twelve dates, like &MTH1, &MTH2, etc.. Then use a define like

MONTH1_BKLOG/I9=IF CREATE_DATE LE &MTH1 AND (CLOSE_DATE GT &MTH1 OR CLOSE_DATE EQ MISSING) THEN 1 ELSE 0;
MONTH2_BKLOG/I9=IF CREATE_DATE LE &MTH2 AND (CLOSE_DATE GT &MTH2 OR CLOSE_DATE EQ MISSING) THEN 1 ELSE 0;


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Platinum Member
posted Hide Post
DM is the most powerful/effecient way to control execution flow in a FOCUS pgm, I would first sincerely suggest reviewing that "do-not-use" rule in the first place.

You can do the date manipulation inside a DEFINE statement by assigining Month & Last-day values in each iteration of a -REPEAT loop.

One suggestion -
  
-REPEAT RPT_LOOP FOR &I FROM 1 TO 12;
DEFINE CLEAR *
DEFINE FILE xxxx
YR/A4='2009';
MTH/A2= IF &I < 10 THEN ('0' || EDIT(&I))
        ELSE EDIT(&I) ;
DY/A2=DECODE &I(1 31 2 28 3 31 .....12 31);
DTE/A10= YR || '-' || MTH || '-' || DY ;
END
TABLE FILE xxx
PRINT xxxxxx
WHERE XXX_DT1 < DTE
END
-RPT_LOOP


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
you can't put an entire define within a repeat loop - each time the loop is executed the previous define will be erased. But the idea is feasible, although there's a much easier way to calculate your end-of month dates using DATEADD and DATEMOV.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Platinum Member
posted Hide Post
You can do it in the following manner also -- this uses older subroutines. You could place it in a loop and index your &vars with .&ctr. Although the new functions are much easier to code.
 
-SET &REMAINDER = IMOD(&CURYR, 4, 'I3L');
-SET &EOM = IF ('&CLOSEMM.EVAL' EQ '02') AND (&REMAINDER EQ 0) THEN '29' ELSE
- IF ('&CLOSEMM.EVAL' EQ '02') AND (&REMAINDER NE 0) THEN '28' ELSE
- IF ('&CLOSEMM.EVAL' EQ '01' OR '03' OR '05' OR '07' OR '08' OR '10' OR '12') THEN '31' ELSE '30';


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Report This Post
Silver Member
posted Hide Post
Thanks to you all! I ended up using Darin's first suggestion. I appreciate the help!


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
Platinum Member
posted Hide Post
Linus, I liked your suggestion, but if I can code less and get the same result - I choose that, so my take on your code would be the same except the last line would be:
quote:


-SET &REMAINDER = IMOD(&CURYR, 4, 'I3L');
-SET &EOM = IF ('&CLOSEMM.EVAL' EQ '02') AND (&REMAINDER EQ 0) THEN '29' ELSE
- IF ('&CLOSEMM.EVAL' EQ '02') AND (&REMAINDER NE 0) THEN '28' ELSE
- IF ('&CLOSEMM.EVAL' EQ '04' OR '06' OR '09') THEN '30' ELSE '31';


Ira

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


aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
 
Posts: 195 | Registered: October 27, 2006Report This Post
Expert
posted Hide Post
Ira,

So, November has 31 days? Smiler


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
Also, I am a little surprised EOM function has not been suggested:

  
-SET &CUR_DT0  = DATECVT(DATEADD(DATECVT (&YYMD,   'I8YYMD', 'YYMD'),'M', -5 ), 'YYMD','I8YYMD');
-SET &EOM_DT0  = DATECVT(DATEMOV(DATECVT(&CUR_DT0, 'I8YYMD', 'YYMD'),   'EOM'),'YYMD', 'I8YYMD');

-SET &CUR_DT1  = DATECVT(DATEADD(DATECVT (20090220,'I8YYMD', 'YYMD'),'M', -12), 'YYMD','I8YYMD');
-SET &EOM_DT1  = DATECVT(DATEMOV(DATECVT(&CUR_DT1, 'I8YYMD', 'YYMD'),   'EOM'),'YYMD', 'I8YYMD');

-SET &CUR_DT2  = DATECVT(DATEADD(DATECVT (20080220,'I8YYMD', 'YYMD'),'M', -12), 'YYMD','I8YYMD');
-SET &EOM_DT2  = DATECVT(DATEMOV(DATECVT(&CUR_DT2, 'I8YYMD', 'YYMD'),   'EOM'),'YYMD', 'I8YYMD');

-SET &CUR_DT3  = DATECVT(DATEADD(DATECVT (20070220,'I8YYMD', 'YYMD'),'M', -12), 'YYMD','I8YYMD');
-SET &EOM_DT3  = DATECVT(DATEMOV(DATECVT(&CUR_DT3, 'I8YYMD', 'YYMD'), 'EOM'),'YYMD', 'I8YYMD');

-TYPE EOM_DT0: &EOM_DT0
-TYPE EOM_DT1: &EOM_DT1
-TYPE EOM_DT2: &EOM_DT2
-TYPE EOM_DT3: &EOM_DT3
-EXIT

Output:

 EOM_DT0: 20090228
 EOM_DT1: 20080229
 EOM_DT2: 20070228
 EOM_DT3: 20060228



Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
quote:
You can still calculate the end of month date for the last 12 months using dialogue manager and then compare your fields' values to those & vars.

So calculate in a loop the twelve dates, like &MTH1, &MTH2, etc..


That was what I was suggesting without giving too much away. Wink Subtract a month from today's date and then move to end of month (times 12).


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report 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     [CLOSED] looping through 12 months with WebFocus

Copyright © 1996-2020 Information Builders