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,

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.

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.

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.

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.

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?

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?

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.

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;

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