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

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Date week
 Login/Join
 
Member
posted
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
 
Posts: 3 | Location: Portland, OR | Registered: August 30, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 3 | Location: Portland, OR | Registered: August 30, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 3 | Location: Portland, OR | Registered: August 30, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Confused


Waz...

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

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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.
 
Posts: 3040 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
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
 
Posts: 30 | Location: Portland, Oregon, USA | Registered: September 11, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 


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