(RESOLVED) Using -REPEAT to count date regressions
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,
JoeThis 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.
August 07, 2010, 08:45 PM
Dan Satchell
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
August 09, 2010, 09:22 AM
Joe_So_Cool
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.
August 10, 2010, 06:19 PM
Dan Satchell
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
August 11, 2010, 07:04 AM
Tony A
quote:
Dialogue Manager isn't very good at performing date calculations involving quarters
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
August 11, 2010, 08:31 AM
Dave
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
August 11, 2010, 08:36 AM
Joe_So_Cool
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.
August 11, 2010, 11:16 AM
Dan Satchell
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:
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?
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
August 11, 2010, 01:01 PM
Dan Satchell
Tony, based on Tom's post I found I had to make the following changes to get your code to work for me:
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
