Focal Point
HOW TO SUM BY WEEK (7 DAY INTERVAL)

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6131075082

April 15, 2008, 02:13 PM
SamF
HOW TO SUM BY WEEK (7 DAY INTERVAL)
Greetings Ginny and all the rest of you smart folks out there! I have yet another challenge. The customer asked that the 'monthly' report, which I now have working very nicely thanks to your help, be subtotaled by 'weekly' intervals in a new Weekly Totals Report. They want the report to be exactly the same except instead of reporting by MONTH/YEAR, it will be reported by WEEK/MONTH/YEAR. I'm not able to use the MATCH logic as there are not exact matches for the full date as there were in MONTH/YEAR. Any suggestions would be grately appreciated! Here is the code of the existing MONTH/YEAR Report...

-* File Ginny2.fex
-SET &FROM_YRMTH_INPUT = '12/2007';
-SET &TO_YRMTH_INPUT = '02/2009';
-SET &FROM_YRMTH = EDIT('&FROM_YRMTH_INPUT.EVAL','\$\$\$9999')|EDIT('&FROM_YRMTH_INPUT.EVAL','99');
-SET &TO_YRMTH = EDIT('&TO_YRMTH_INPUT.EVAL','\$\$\$9999')|EDIT('&TO_YRMTH_INPUT.EVAL','99');
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-TYPE FROM_YRMTH &FROM_YRMTH
-TYPE TO_YRMTH &TO_YRMTH
-TYPE MnthCount &MnthCount

-* Create a temporary master for all the required months.
FILEDEF MOLISTMAS DISK monthlist.mas
-RUN
-WRITE MOLISTMAS FILENAME=MONTHLIST,SUFFIX=FIX
-WRITE MOLISTMAS SEGNAME=DATESEG,SEGTYPE=S0
-WRITE MOLISTMAS FIELDNAME=DATEYYM,,FORMAT=YYM,ACTUAL=A6,\$

-* Load the temp file with all the months. To be used in the match later.
FILEDEF MONTHLIST DISK monthlist.ftm
-RUN
-REPEAT LOADLOOP FOR &Cntr FROM 0 TO &MnthCount ;
-SET &LdCnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-WRITE MONTHLIST &LdCnYrMth
-TYPE MnthList &LdCnYrMth

SET ASNAMES=ON

DEFINE FILE TBLBEREAVEMENTBURIAL
RPTDTCLSD/YYM = HDATE(DATECLOSED, 'YYMD');
RPTDTRCVD/YYM =
ELSE
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTCLSD AS DATECNT1
BY RPTDTCLSD AS DATEYYM
WHERE RPTDTCLSD FROM &FROM_YRMTH TO &TO_YRMTH
WHERE RPTDTRCVD LT RPTDTCLSD
ON TABLE HOLD AS LASTWKCO FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD LASTWKCO

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTRCVD AS DATECNT2
BY RPTDTRCVD AS DATEYYM
WHERE RPTDTRCVD FROM &FROM_YRMTH TO &TO_YRMTH
ON TABLE HOLD AS NEWREFERRALS FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD NEWREFERRALS

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTRCVD AS DATECNT3
BY RPTDTRCVD AS DATEYYM
WHERE RPTDTRCVD FROM &FROM_YRMTH TO &TO_YRMTH
WHERE DATECLOSED IS MISSING
ON TABLE HOLD AS NXTWKCO FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD NXTWKCO

MATCH FILE MONTHLIST
BY DATEYYM
RUN
FILE LASTWKCO
PRINT DATECNT1
BY DATEYYM
RUN
FILE NEWREFERRALS
PRINT DATECNT2
BY DATEYYM
RUN
FILE NXTWKCO
PRINT DATECNT3
BY DATEYYM
AFTER MATCH HOLD AS DATECOUNTS OLD-OR-NEW
END

TABLE FILE DATECOUNTS
PRINT DATECNT1 AS 'Last Week Carry Overs'
DATECNT2 AS 'New Referrals'
DATECNT3 AS 'Next Week Carry Overs'
BY DATEYYM AS 'Year / Month'
END

Thanks!
SamIam

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF
April 15, 2008, 02:25 PM
Darin Lee
Just define another field where you have used DATEMOV to convert DATECLOSED to a beginning of week (BOW) or end of week (EOW). Then add that as an additional sort. The trick, however, is getting it to look right when a week is split up by the end of a month.

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
April 15, 2008, 03:44 PM
susannah
sam
the HPART function has some wierd wrinkles, but you can get a week number for each date in your original data pull..
Having said that tho, i'm now really liking Darin's idea of just defining a new field that takes every day and assigns it to a date that is beginnig of week..that gets around the problems with HPART...

say Sam, who do you work for?

 In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
April 15, 2008, 04:13 PM
SamF
Darin, that worked like a charm! Thank you very kindly!!

Suzannah, thanks for the HPART input. I'll put that in my notes! Also, I work for Harris County, Texas. Why do you ask?

Here is my final code for any newbies out there who are following along...

-* File weeklyreport.fex
-SET &FR_MDYY_INPUT = '01/01/2007';
-SET &TO_MDYY_INPUT = '02/01/2007';
-SET &FR_YYMD = EDIT('&FR_MDYY_INPUT.EVAL','\$\$\$\$\$\$9999')|EDIT('&FR_MDYY_INPUT.EVAL','99')|EDIT('&FR_MDYY_INPUT.EVAL','\$\$\$99');
-SET &PREV_YYMD = &FR_YYMD;
-SET &TO_YYMD = EDIT('&TO_MDYY_INPUT.EVAL','\$\$\$\$\$\$9999')|EDIT('&TO_MDYY_INPUT.EVAL','99')|EDIT('&TO_MDYY_INPUT.EVAL','\$\$\$99');
-SET &LdYYMD = &FR_YYMD;
-SET &MonthName = CHGDAT('YYMD', 'MX', &FR_YYMD, 'A15');
-SET &DSPL_FRDT = EDIT(&MonthName)|' '|EDIT('&FR_MDYY_INPUT.EVAL','\$\$\$99')|' '|EDIT('&FR_MDYY_INPUT.EVAL','\$\$\$\$\$\$9999');
-SET &MonthName = CHGDAT('YYMD', 'MX', &TO_YYMD, 'A15');
-SET &DSPL_TODT = EDIT(&MonthName)|' '|EDIT('&TO_MDYY_INPUT.EVAL','\$\$\$99')|' '|EDIT('&TO_MDYY_INPUT.EVAL','\$\$\$\$\$\$9999');
-SET &Cntr = 1;

-* Create a temporary master for all the required Weeks.
FILEDEF WKLISTMAS DISK weeklist.mas
-RUN
-WRITE WKLISTMAS FILENAME=WEEKLIST,SUFFIX=FIX
-WRITE WKLISTMAS SEGNAME=DATESEG,SEGTYPE=S0
-WRITE WKLISTMAS FIELDNAME=DATEYYMD,,FORMAT=YYMD,ACTUAL=A8,\$

-* Load the temp file with all the Weeks. To be used in the match later.
FILEDEF WEEKLIST DISK weeklist.ftm
-RUN
-REPEAT LOADLOOP WHILE &LdYYMD LE &TO_YYMD;
-WRITE WEEKLIST &LdYYMD
-SET &LdYYMD = AYMD(&PREV_YYMD, 7, 'I8');
-TYPE To &LdYYMD
-SET &PREV_YYMD = &LdYYMD;

SET ASNAMES=ON

DEFINE FILE TBLBEREAVEMENTBURIAL
RPTDTCLSD/YYMD =
IF DATECLOSED LT DT(00010101000000) THEN
'17990101'
ELSE
HDATE(DATECLOSED, 'YYMD');
RPTDTBURY/YYMD =
IF DATEBURIAL LT DT(00010101000000) THEN
'17990101'
ELSE
HDATE(DATEBURIAL, 'YYMD');
RPTDTRCVD/YYMD =
ELSE
CNTR/I4 = 0;
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT RPTDTBURY AS WKCOUNT1 AND
COMPUTE DATEYYMD/YYMD = DATEMOV(RPTDTBURY, 'BOW');
BY RPTDTBURY
WHERE RPTDTBURY FROM &FR_YYMD TO &TO_YYMD
WHERE CEMETERY = 'Harris County Cemetery'
ON TABLE HOLD AS ADLTBURY FORMAT ALPHA
-*ON TABLE SUBTOTAL
END
-*-EXIT
-RUN

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT RPTDTBURY AS WKCOUNT2 AND
COMPUTE DATEYYMD/YYMD = DATEMOV(RPTDTBURY, 'BOW');
BY RPTDTBURY
WHERE RPTDTBURY FROM &FR_YYMD TO &TO_YYMD
WHERE CEMETERY = 'Harris County Cemetery'
WHERE TYPE = 'Baby Burial'
ON TABLE HOLD AS BABYBURY FORMAT ALPHA
-*ON TABLE SUBTOTAL
END
-*-EXIT
-RUN
? HOLD BABYBURY

MATCH FILE WEEKLIST
BY DATEYYMD
RUN
PRINT WKCOUNT1
BY DATEYYMD
RUN
FILE BABYBURY
PRINT WKCOUNT2
BY DATEYYMD
AFTER MATCH HOLD AS DATECOUNTS OLD-OR-NEW
END

TABLE FILE DATECOUNTS
PRINT
WKCOUNT1 AS '
Buried
'
WKCOUNT2 AS '
Babies
Buried
'
COMPUTE '
Total Burials
at HC Cemetery
'/D3 = WKCOUNT1 + WKCOUNT2;
BY DATEYYMD AS '
For The
Week Of
'
""
"Run Date: &DATEtrMDYY <+0> "
""
"Harris County Social Services"
"Bereavement Summary Report"
"&DSPL_FRDT To &DSPL_TODT"
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Legal',
SQUEEZE=ON,
ORIENTATION='Landscape',
WRAP=ON,
\$
LINE=2,
SIZE=9,
STYLE=NORMAL,
JUSTIFY=LEFT,
\$
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=9,
\$
LINE=4,
SIZE=11,
STYLE=NORMAL,
JUSTIFY=CENTER,
\$
LINE=4,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
STYLE=BOLD,
\$
LINE=5,
SIZE=11,
STYLE=NORMAL,
JUSTIFY=CENTER,
\$
LINE=5,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
STYLE=BOLD,
\$
LINE=6,
SIZE=11,
STYLE=NORMAL,
JUSTIFY=CENTER,
\$
LINE=6,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
STYLE=BOLD,
\$
LINE=7,
SIZE=11,
STYLE=BOLD,
JUSTIFY=CENTER,
\$
LINE=7,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
\$
TYPE=DATA,
FONT='CALIBRI',
SIZE=11,
JUSTIFY=CENTER,
\$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
\$
SIZE=10,
STYLE=BOLD,
\$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
\$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
\$
TYPE=ACROSSVALUE,
SIZE=9,
\$
TYPE=ACROSSTITLE,
STYLE=BOLD,
\$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
\$
ENDSTYLE
END

Thanks very much once again!!
Sam

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF
April 15, 2008, 04:23 PM
susannah
TABLE FILE TBLBEREAVEMENTBURIALS
is a lot more colorful than
TABLE FILE CAR.

 In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
April 15, 2008, 04:44 PM
Prarie
I agree Susannah...I was thinking funeral home.

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

 In Focus since 1993. WebFOCUS 7.7.03 Win 2003
LOL! That would've been more interesting!! Sorry for the 'grave' subject matter...

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF
After going to the last user Forum here and seeing what the Houston Police Department are doing with WF, I am afraid to ask where the bodies are buried.

Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
Don't ask - if we told you, we'd have to kill you...

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF