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     (RESOLVED) Using -REPEAT to count date regressions

Read-Only Read-Only Topic
Go
Search
Notify
Tools
(RESOLVED) Using -REPEAT to count date regressions
 Login/Join
 
Member
posted
Hi all,
I need to count backwards from each Quarter (03/YY, 06/YY, 09/YY, 12/YY) to a base date in 2008. Can I use repeat in this way:

-REPEAT DT WHILE &BHDATE LE &EODATE;
-INCLUDE XXXXXXXX
-RUN
-DT

In this code &BHDATE is the base date and &EODATE is the regressing date; the -INCLUDE XXXXXXXX is a repetitive quarterly regression/summarize. I know that a semi-colon is supposed to not be necessary, but it gets an error when I don't use it..

At each point I add 1 to a counter to use later in the program.

I use FOCUS v7 on z/OS.

In addition, I need to use this counter number to develop a report which shows totals for each regressing total along with the dates of each regressed quarter. Can I use this in a subfoot that includes the totals for each quarter AND the start/end dates for each quarter, and how do I do this if it's possible. If not, I am open to suggestions as to how to accomplish this.

Thanks in advance,

Joe

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


Mainframe FOCUS
z/OS
Standard paper reporting and VPS interface for emailing Excel, Word, etc.
 
Posts: 6 | Registered: October 21, 2009Report This Post
Virtuoso
posted Hide Post
What is the output format for your reports? Is the output target on-line reports or report files? What kind of dates are BHDATE and EODATE?

Below is one technique for producing Excel output. BYTOC generates a separate Excel tab for each quarter between the begin and end dates for the report. BY HIGHEST sorts the quarters and tabs from most recent to oldest. The MOVIES file is a standard sample file like the CAR file.

-SET &BHDATE = 19870904 ;
-SET &EODATE = &YYMD ;
-*
DEFINE FILE MOVIES
 RELQTR/YYQ = RELDATE ;
END 
-*
TABLE FILE MOVIES
 SUM COPIES
 BY HIGHEST RELQTR
 WHERE RELDATE FROM '&BHDATE' TO '&EODATE';
 ON TABLE PCHOLD FORMAT EXL2K BYTOC
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
Hi Dan,
Thanks for responding.

In reponse to your queries:
a)Q] - "What is the output format for your reports? Is the output target on-line reports or report files?"
A1] - the output format will be both a file and one a report, which will be sent via email in .pdf format;
A2] - the other (the file) will be tranmitted via Connect/Direct (used to be Network Data Mover).

b)Q[ - "What kind of dates are BHDATE and EODATE?"
A1] - They are in MDY order, and have never been formatted as YMD or YYMD, smart date or otherwise, in my system;
A2 - BHDATE is the base date/EODATE is the present quarterly date.


Mainframe FOCUS
z/OS
Standard paper reporting and VPS interface for emailing Excel, Word, etc.
 
Posts: 6 | Registered: October 21, 2009Report This Post
Virtuoso
posted Hide Post
Here's some sample code. Dialogue Manager isn't very good at performing date calculations involving quarters, which is why I used DEFINE/TABLE/-READ to do so. This code determines the number of quarters between the start and end dates and then uses a -REPEAT loop to create a PDF report file for each quarter. I used YYMD formats because that is the date format used in the MOVIES file. My report file names are of the format Fyyyyq.PDF and are written to the BASEAPP folder. This code will hopefully give you some ideas.

-SET &BHDATE = 19870904 ;
-SET &EODATE = 19881016 ;
-*
-* Determine current quarter and number of quarters to report.
DEFINE FILE MOVIES
 BHDATE/YYMD WITH MOVIECODE = '&BHDATE';
 EODATE/YYMD WITH MOVIECODE = '&EODATE';
 BASE_QTR/YYQ = BHDATE ;
 CURR_QTR/YYQ = EODATE ;
 QTR_COUNT/I5 = CURR_QTR - BASE_QTR ;
END
-*
TABLE FILE MOVIES
 PRINT CURR_QTR QTR_COUNT
 WHERE RECORDLIMIT EQ 1 ;
 ON TABLE SAVE AS QTRCNT
END
-RUN
-READ QTRCNT &CURR_QTR.5 &QTR_COUNT.5
-*TYPE &CURR_QTR &QTR_COUNT
-*------------------------------------------------
-REPEAT ENDREPEAT1 FOR &I FROM 0 TO &QTR_COUNT
-*
-* Determine report quarter and begin and end dates for quarter (for current loop iteration).
DEFINE FILE MOVIES
 CURR_QTR/YYQ WITH MOVIECODE = &CURR_QTR ;
 RPT_QTR/YYQ  = CURR_QTR - &I ;
 QTR_BEG/YYMD = RPT_QTR ;
 QTR_END/YYMD = DATEMOV(QTR_BEG,'EOQ');
END
-*
TABLE FILE MOVIES
 PRINT RPT_QTR QTR_BEG QTR_END
 WHERE RECORDLIMIT EQ 1 ;
 ON TABLE SAVE AS BEGEND
END
-RUN
-READ BEGEND &RPT_QTR.5 &QTR_BEG.8 &QTR_END.8
-*TYPE &RPT_QTR &QTR_BEG &QTR_END
-*
-* Specify output file name and location.
APP FILEDEF OUTFILE DISK BASEAPP/F&RPT_QTR...PDF
-*
-* Generate report for current loop iteration, using quarter begin and end dates.
TABLE FILE MOVIES
 SUM COPIES
 BY RELDATE
 WHERE RELDATE FROM '&QTR_BEG' TO '&QTR_END';
 ON TABLE SAVE AS OUTFILE FORMAT PDF
END
-RUN
-*
-ENDREPEAT1


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
quote:
Dialogue Manager isn't very good at performing date calculations involving quarters
Hmmm .........

How about -
-SET &Qtr_1BOQ = DATECVT(DATEMOV(&YYMD, 'BOQ'), 'YYMD', 'I8YYMD');
-SET &Qtr_1EOQ = DATECVT(DATEMOV(&YYMD, 'EOQ'), 'YYMD', 'I8YYMD');
-SET &Qtr_2BOQ = DATECVT(DATEMOV(DATEADD(&YYMD, 'D', -91), 'BOQ'), 'YYMD', 'I8YYMD');
-SET &Qtr_2EOQ = DATECVT(DATEMOV(DATEADD(&YYMD, 'D', -91), 'EOQ'), 'YYMD', 'I8YYMD');
-SET &Qtr_3BOQ = DATECVT(DATEMOV(DATEADD(&YYMD, 'D', -182), 'BOQ'), 'YYMD', 'I8YYMD');
-SET &Qtr_3EOQ = DATECVT(DATEMOV(DATEADD(&YYMD, 'D', -182), 'EOQ'), 'YYMD', 'I8YYMD');
-SET &Qtr_4BOQ = DATECVT(DATEMOV(DATEADD(&YYMD, 'D', -273), 'BOQ'), 'YYMD', 'I8YYMD');
-SET &Qtr_4EOQ = DATECVT(DATEMOV(DATEADD(&YYMD, 'D', -273), 'EOQ'), 'YYMD', 'I8YYMD');
-? &Q
 CURRENTLY DEFINED & VARIABLES STARTING WITH 'Q':
 &Qtr_1BOQ     = 20100701
 &Qtr_1EOQ     = 20100930
 &Qtr_2BOQ     = 20100401
 &Qtr_2EOQ     = 20100630
 &Qtr_3BOQ     = 20100101
 &Qtr_3EOQ     = 20100331
 &Qtr_4BOQ     = 20091001
 &Qtr_4EOQ     = 20091231

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
Master
posted Hide Post
Other solution... maybe.. ( we use this a lot )

table file .... 
sum value
by timeperiod1
by timeperiod2
on table hold as x format focus
end

table file x
sum value as 'actual value in timeperiod'
compute value2/d12 = value + value2; as 'running value until timeperiod'
by timeperiod1
by timeperiod2
end


And you could make the compute smarter... ( for multiple breaks )
if lst.timeperiod1 eq last lst.timeperiod1 then value2+value else value; 


This way any next timeperiod value equals all previous values.

in this what you mean?


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Member
posted Hide Post
Thanks Dan and Tony and Dave, I have what I need to move forward.


Mainframe FOCUS
z/OS
Standard paper reporting and VPS interface for emailing Excel, Word, etc.
 
Posts: 6 | Registered: October 21, 2009Report This Post
Virtuoso
posted Hide Post
Tony, I must be doing something wrong because these are my results when I copy, paste, and run your code in Dev Studio 7.6.6:

 &Qtr_1BOQ     = ********
 &Qtr_1EOQ     = ********
 &Qtr_2BOQ     = ********
 &Qtr_2EOQ     = ********
 &Qtr_3BOQ     = ********
 &Qtr_3EOQ     = ********
 &Qtr_4BOQ     = ********
 &Qtr_4EOQ     = ********

According to my Quick Reference guide for 7.6.2, functions DATEMOVE, DATEADD, and DATECVT are not supported in Dialogue Manager. Has this changed in 7.7 (or earlier), or am I missing something? Also, are you now using global positioning coordinates to pinpoint your location in your signature?


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
Dan, we use them all the time! Smiler

  
-SET &LST_MO     = DATECVT(DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')),'M', - 1 ),'YYMD','I8YYMD');
-SET &DT_EOM     = DATECVT(( DATEMOV((DATECVT(&LST_MO,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-SET &ENG_DATE1  = LCWORD(17,CHGDAT('YYMD', 'MDYYX', &DT_EOM.EVAL, 'A17'), 'A17');
-SET &ENG_MO     = GETTOK('&ENG_DATE1.EVAL', 17, 1, ' ', 17, 'A17');
-SET &ENG_DA     = GETTOK('&ENG_DATE1.EVAL', 17, 2, ' ', 17, 'A17');
-SET &ENG_YR     = GETTOK('&ENG_DATE1.EVAL', 17, 3, ' ', 17, 'A17');
-SET &ENG_DATE   = &ENG_MO || (' ' | &ENG_DA) || ',' || (' ' | &ENG_YR);
-TYPE &ENG_DATE


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
Hi Dan,
quote:
functions DATEMOVE, DATEADD, and DATECVT are not supported in Dialogue Manager
As you have noticed, I am using 7.7.01 and that is the release in which I wrote the code. However, I am sure (as per Tom above) that I have done this in earlier releases. Not sure why your responses are different.

quote:
Also, are you now using global positioning coordinates to pinpoint your location
Someone who reads more than the post! How observant of you. It is a tad uncomfortable sitting here though!

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
Virtuoso
posted Hide Post
Tony, based on Tom's post I found I had to make the following changes to get your code to work for me:

-SET &Qtr_1BOQ = DATECVT(DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_1EOQ = DATECVT(DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'EOQ'),'YYMD','I8YYMD');
-SET &Qtr_2BOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-91),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_2EOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-91),'EOQ'),'YYMD','I8YYMD');
-SET &Qtr_3BOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-182),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_3EOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-182),'EOQ'),'YYMD','I8YYMD');
-SET &Qtr_4BOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-273),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_4EOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-273),'EOQ'),'YYMD','I8YYMD');

So I wonder if DATEMOVE and DATEADD work in version 7.7 in Dialogue Manager without the need to use DATECVT? You might try this and see what happens.

-SET &Qtr_1BOQ = DATEMOV(&YYMD,'BOQ');
-SET &Qtr_1EOQ = DATEMOV(&YYMD,'EOQ');
-SET &Qtr_2BOQ = DATEMOV(DATEADD(&YYMD,'D',-91),'BOQ');
-SET &Qtr_2EOQ = DATEMOV(DATEADD(&YYMD,'D',-91),'EOQ');
-SET &Qtr_3BOQ = DATEMOV(DATEADD(&YYMD,'D',-182),'BOQ');
-SET &Qtr_3EOQ = DATEMOV(DATEADD(&YYMD,'D',-182),'EOQ');
-SET &Qtr_4BOQ = DATEMOV(DATEADD(&YYMD,'D',-273),'BOQ');
-SET &Qtr_4EOQ = DATEMOV(DATEADD(&YYMD,'D',-273),'EOQ');
-? &Q


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
I have found that you must call DATECVT to first convert to a date format, then call the function, then call DATECVT to convert back to a string.

Does it behave differently in v7.7.01? Only Tony knows...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Yes, but apparently Tony is sitting atop the lighthouse in Portsmouth, England overlooking the straits that separate Portsmouth from Gosport.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
And he gets v7.7.01 before us? Ah! The life of a lighthouse keeper...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Nope, my bet is what he had before the global positioning coordinates, over a big ocean beginning with an "I"...


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
quote:
lighthouse in Portsmouth
Not a lighthouse Dan, but the spinnaker tower - a millenium project that was finished after the start of the millenium!

T
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Member
posted Hide Post
Dan, As a refinement to your technique:

-SET &Qtr_1BOQ = DATECVT(DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_1EOQ = DATECVT(DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'EOQ'),'YYMD','I8YYMD');
-SET &Qtr_2BOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-91),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_2EOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-91),'EOQ'),'YYMD','I8YYMD');
-SET &Qtr_3BOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-182),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_3EOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-182),'EOQ'),'YYMD','I8YYMD');
-SET &Qtr_4BOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-273),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_4EOQ = DATECVT(DATEMOV(DATEADD(DATECVT(&YYMD,'I8YYMD','YYMD'),'D',-273),'EOQ'),'YYMD','I8YYMD');

I used the beginning of a quarter to calculate the end of the previous quarter:

-SET &Qtr_1EOQ = DATECVT(DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'EOQ'),'YYMD','I8YYMD');
-SET &Qtr_1BOQ = DATECVT(DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_2EOQ = DATECVT(DATEADD(DATECVT(&Qtr_1BOQ,'I8YYMD','YYMD'),'D',-1),'YYMD','I8YYMD');
-SET &Qtr_2BOQ = DATECVT(DATEMOV(DATECVT(&Qtr_2EOQ,'I8YYMD','YYMD'),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_3EOQ = DATECVT(DATEADD(DATECVT(&Qtr_2BOQ,'I8YYMD','YYMD'),'D',-1),'YYMD','I8YYMD');
-SET &Qtr_3BOQ = DATECVT(DATEMOV(DATECVT(&Qtr_3EOQ,'I8YYMD','YYMD'),'BOQ'),'YYMD','I8YYMD');
-SET &Qtr_4EOQ = DATECVT(DATEADD(DATECVT(&Qtr_3BOQ,'I8YYMD','YYMD'),'D',-1),'YYMD','I8YYMD');
-SET &Qtr_4BOQ = DATECVT(DATEMOV(DATECVT(&Qtr_4EOQ,'I8YYMD','YYMD'),'BOQ'),'YYMD','I8YYMD');
-? &Qtr

This reduces the nesting of of date functions by one for calculating previous quarters. Your technique is really slick by using DATECVT twice. I'm an old coder that use to write a DEFINE FILE, TABLE FILE and -READ

Dave D.
 
Posts: 8 | Location: Information Builders Chicago Branch | Registered: November 15, 2005Report This Post
Virtuoso
posted Hide Post
quote:
-SET &Qtr_1BOQ = DATECVT(DATEMOV(DATECVT(&YYMD,'I8YYMD','YYMD'),'BOQ'),'YYMD','I8YYMD');
-


For some reason I always seems to need the use of EVAL in those set formulas

-SET &Qtr_1BOQ = DATECVT(DATEMOV(DATECVT(&YYMD.EVAL,'I8YYMD','YYMD'),'BOQ'),'YYMD','I8YYMD');
-




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Member
posted Hide Post
Frank -

Sometimes &var.EVAL is needed when you enclose the variable in quotes.

For example: -SET &DT_MONTH = GETTOK('&DT_STRING.EVAL',17,1,' ',17,'A17');

If you left off the .EVAL, GETTOK would parse "&DT_STRING" instead of the value it represents. When in doubt, it never hurts to include it.

-Dave D.
 
Posts: 8 | Location: Information Builders Chicago Branch | Registered: November 15, 2005Report 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     (RESOLVED) Using -REPEAT to count date regressions

Copyright © 1996-2020 Information Builders