Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2010Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2010Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2010Report This Post
Expert
posted Hide Post
Confused


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2005Report 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.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report 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, 2006Report 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, 2009Report 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, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders