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
-LOADLOOP

SET ASNAMES=ON

DEFINE FILE TBLBEREAVEMENTBURIAL
RPTDTCLSD/YYM = HDATE(DATECLOSED, 'YYMD');
RPTDTRCVD/YYM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
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

Posts: 70 | Registered: January 25, 2008

Darin Lee

Virtuoso

posted

Hide Post

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

Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007

Ignored post by Darin Lee posted

Show Post

susannah

Expert

posted

Hide Post

sam
read this thread calculating weeks
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

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

SamF

Gold member

posted

Hide Post

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;
-LOADLOOP

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 =
IF DATERECEIVED LT DT(00010101000000) THEN
HDATE(DATEADDED, 'YYMD')
ELSE
HDATE(DATERECEIVED, 'YYMD');
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'
WHERE TYPE = 'Adult Burial'
ON TABLE HOLD AS ADLTBURY FORMAT ALPHA
-*ON TABLE SUBTOTAL
END
-*-EXIT
-RUN
? HOLD ADLTBURY

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
FILE ADLTBURY
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 'Adults
Buried'
WKCOUNT2 AS 'Babies
Buried'
COMPUTE 'Total Burials
at HC Cemetery'/D3 = WKCOUNT1 + WKCOUNT2;
BY DATEYYMD AS 'For The
Week Of'
HEADING
""
"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,
$
TYPE=HEADING,
LINE=2,
SIZE=9,
STYLE=NORMAL,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=9,
$
TYPE=HEADING,
LINE=4,
SIZE=11,
STYLE=NORMAL,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=4,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=5,
SIZE=11,
STYLE=NORMAL,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=5,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=6,
SIZE=11,
STYLE=NORMAL,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=6,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=7,
SIZE=11,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=7,
OBJECT=TEXT,
ITEM=1,
SIZE=11,
$
TYPE=DATA,
FONT='CALIBRI',
SIZE=11,
JUSTIFY=CENTER,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
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

Posts: 70 | Registered: January 25, 2008

Ignored post by SamF posted

Show Post

susannah

Expert

posted

Hide Post

sam, i ask because
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

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

Prarie

Virtuoso

posted

Hide Post

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

Posts: 1903 | Location: San Antonio | Registered: February 28, 2005

Ignored post by Prarie posted

Show Post

SamF

Gold member

posted

Hide Post

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

Posts: 70 | Registered: January 25, 2008

Ignored post by SamF posted

Show Post

PBrightwell

Master

posted

Hide Post

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

Posts: 755 | Location: TX | Registered: September 25, 2007

Ignored post by PBrightwell posted

Show Post

SamF

Gold member

posted

Hide Post

Don't ask - if we told you, we'd have to kill you...