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.
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
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
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
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?
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. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
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.
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