Focal Point
[CLOSED] Date week

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

October 20, 2010, 12:36 PM
nshaffer
[CLOSED] Date week
I am running FOCUS V7.6.10.
to use the field: DT_WEEK/I2 = HPART(CUR_HMDYY,'WEEK','I2'); I get the following results:
PAY WEEK TOTAL PAID US$ # OF CLAIMS
-------- -------------- -----------
42 159,482.88 718

is it possible to get all of the weeks for the current year not just the current week? If so what is the coding to use?

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


FOCUS V7.6.10
Windows XP, Excel
October 20, 2010, 05:14 PM
Waz
You are using a field called CUR_HMDYY. This seems to be the current date.

Do you have another date field that will give you what you want.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

October 20, 2010, 05:34 PM
nshaffer
PAY WEEK TOTAL PAID US$ # OF CLAIMS
-------- -------------- -----------
OTHER 66,011.88 308
10-WK02 478.00 5
10-WK03 4,733.34 6
10-WK04 231.15 4
10-WK05 397.32 4
10-WK06 1,886.76 7
10-WK07 222.00 6
10-WK08 897.71 4
10-WK09 1,187.75 10
10-WK10 562.33 9
10-WK11 2,100.78 6

This is a sample of data that we are striving for. We currently use a table to create the "Pay Week" and are trying to get away from using tables and just use coding.


FOCUS V7.6.10
Windows XP, Excel
October 20, 2010, 05:50 PM
Waz
What is your data source, the tables used in the query.

What columns are available ?

To split the data into weeks, you need a date stored against the data to do this.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

October 20, 2010, 06:17 PM
nshaffer
Okay thanks, it does not look like I can get by with out using tables. So we will just continue on like we have been. Thank you for your answers.


FOCUS V7.6.10
Windows XP, Excel
October 20, 2010, 06:41 PM
Waz
Confused


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

October 21, 2010, 10:08 AM
njsden
If you don't have supporting data to provide the set of dates/weeks, you could perhaps make it up using the McGyver technique. Search for either McGyver or MacGyver (and perhaps other [mis]spellings) for plenty of examples on how to go around that.

If you have to get all weeks for 2010, you could use HPART to obtain the week number corresponding to the last day of the year. That'll tell you how many "records" you'll need to create with McGyver.

Of course, that won't give you a solution for the "TOTAL PAID US$" and "# OF CLAIMS" values corresponding to each week, which is probably why Waz was asking about your data source and dates available within it. How else could you assign $66,011.88 to Week 2?



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
October 22, 2010, 05:33 PM
Doug
http://forums.informationbuild...731069712#4731069712 c/o: Mickey

More things to check out: BOY, EOY, BOM, EOM...

But, what is the Source of your data? You gotta have something for the base your "Date Week", don't you?


   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Most recent: 8204 Gen 48 in Test and Production.
   Currently Available. Please contact me.
October 25, 2010, 10:17 AM
FrankDutch
I suppose you have a paydate in your database that holds all the payments.
That paydate can be used

I you create a defined field like payweek then you are almost done.

DEFINE FILE WHATEVERDATATABLE
PAYWEEK/YYMD=DATEMOV(PAYDATE,EOW);
END

Now you can create a report that groups all the payments per week as if they where payed on the last day of that week.




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

October 25, 2010, 01:11 PM
j42p11
You can use this long calculation to produce the week number. SUN_WEEKA would be used if you want the first week of the year to begin as week 0, SUN_WEEK would be used to start the first week of the year as week 1.

DATE1/YYMD=&YYMD;
DATE3/I8YYMD=DATE1;
DATE4/I8=DATE3;
WEEKDAY/A8 = DOWK(DATE4,WEEKDAY);
WED_DOWK/I1=DECODE WEEKDAY('MON' 2 'TUE' 3 'WED' 4 'THU' 5 'FRI' 6
'SAT' 7 'SUN' 1);
WED_DATE/YYMD=(DATE1 + 4)-WED_DOWK;
WED_DATE1/I8YYMD=WED_DATE;
WED_DATE2/I8=WED_DATE1;
WED_JULIAN_DATE/I5 = JULDAT(WED_DATE1, WED_JULIAN_DATE);
WED_JULIAN1/A5=EDIT(WED_JULIAN_DATE);
WED_JULIAN2/A3=EDIT(WED_JULIAN1, '$$999');
WED_JULIAN/I3=EDIT(WED_JULIAN2);
SUN_WEEK1/D7.2=WED_JULIAN/7;
SUN_WEEK2/A8=FTOA(SUN_WEEK1, '(D7.2)', SUN_WEEK2);
SUN_WEEK3/A2=EDIT(SUN_WEEK2, '$$$$$$99');
SUN_WEEKA/I3=WED_JULIAN/7;
SUN_WEEK/I3=IF SUN_WEEK3 EQ '00' THEN SUN_WEEKA ELSE SUN_WEEKA + 1;


WebFocus 7.7.03 - 7703_hotfix - Gen: 284
Dev Studio 7.7.03 - Gen: 06062011
Mainframe Focus 7.7.03
Windows WF Client Server
zOS Reporting server (hub-sub config)
Windows Reporting server
Windows RC Client Server
October 26, 2010, 01:23 AM
Dan Satchell
It would seem to me that you have already provided most of the solution to your own question. As earlier contributors to this entry have already suggested, you must have some kind of date associated with each claim and dollar amount. If this is the case, you shouldn't need a date table to produce the sample output you provided. If we assume the date in your data is a date-time field (based on your example using HPART) and the name of that field is, say, PAY_DATE, then something like this should work:

DEFINE FILE <filename>
 WEEK_NUMBER/I2 = HPART(PAY_DATE,'WEEK','I2');
 YEAR_NUMBER/I4 = HPART(PAY_DATE,'YEAR','I4');
 SHORT_YEAR/A2  = EDIT(YEAR_NUMBER,'$$99');
 WEEK_TEXT/A7   = SHORT_YEAR | '-WK' | EDIT(WEEK_NUMBER);
END

TABLE FILE <filename>
 SUM <dollar amount field>     AS 'Total Paid,US $'
     CNT.<claim number field>  AS '# of Claims'
 BY WEEK_TEXT                  AS 'Pay Week'
END



WebFOCUS 7.7.05