Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     HOW TO SUM BY WEEK (7 DAY INTERVAL)
Go
New
Search
Notify
Tools
Reply
  
HOW TO SUM BY WEEK (7 DAY INTERVAL)
 Login/Join
 
Gold member
posted
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, 2008Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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, 2003Reply With QuoteReport This Post
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, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
sam, i ask because
TABLE FILE TBLBEREAVEMENTBURIALS
is a lot more colorful than
TABLE FILE CAR.
Wink




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I agree Susannah...I was thinking funeral home. Wink

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, 2005Reply With QuoteReport This Post
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, 2008Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Don't ask - if we told you, we'd have to kill you... Smiler


WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF
 
Posts: 70 | Registered: January 25, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     HOW TO SUM BY WEEK (7 DAY INTERVAL)

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.