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
Computing a value
 Login/Join
 
eb
Member
posted
Hello,
I'm having a bit of logic issue when programming in WebFOCUS.
Does anyone know of a technique to capture the first value in the month (i.e. FIELD_1) and subtract it from all of the subsequent values in the month?

I know this is incorrect, but this is what I'm trying to accomplish:

TABLE FILE EXAMPLE
SUM FIELD_1
COMPUTE FIRST_DAY_OF_MONTH/I3=FST.FIELD_1 WITHIN MONTH;
COMPUTE FIELD_2=FIELD_1-FIRST_DAY_OF_MONTH;
BY DATE
BY MONTH
END

Desired Output:

DATE.......MONTH...FIELD_1...FIRST_DAY_OF_MONTH_VALUE....FIELD_2
20070101...1.......100............................100.............................0
20070102...1.......200............................100.............................100
20070103...1.......350............................100.............................250
...
20070201...2.......120............................120.............................0
20070202...2.......250............................120.............................130

Any help is appreciated. Thanks!

------------
WebFOCUS 7.6.2
IBM DB2

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


WF 7.6.5 / BID / Info Assist / Report Caster - HTML/PDF/EXCEL
 
Posts: 18 | Registered: September 27, 2007Report This Post
Tom Flynn
Expert
posted Hide Post
quote:
TABLE FILE EXAMPLE
SUM FIELD_1
COMPUTE FIRST_DAY_OF_MONTH/I3=FST.FIELD_1 WITHIN MONTH;
COMPUTE FIELD_2=FIELD_1-FIRST_DAY_OF_MONTH;
BY DATE
BY MONTH
END



Here is 1 idea:

  
SET ASNAMES = ON
TABLE FILE EXAMPLE
SUM 
    FST.FIELD_1 AS 'BASE_AMT'
  BY MONTH
 ON TABLE HOLD AS HOLD1
END
-RUN

TABLE FILE EXAMPLE
SUM 
    FIELD_1
  BY MONTH
  BY DATE
 ON TABLE HOLD AS HOLD2
END
-RUN

JOIN MONTH IN HOLD1 TO ALL MONTH IN HOLD2 AS J1
-RUN

TABLE FILE HOLD1
SUM
    MONTH
    BASE_AMT
    FIELD_1
   COMPUTE FIELD_2/P14.2C = BASE_AMT - FIELD1;
  BY MONTH NOPRINT
  BY DATE
END
-EXIT


Hope this, and, the other's that follow, help...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
eb
Member
posted Hide Post
Thanks Tom!
That seems to work, only problem is I'm working with somewhat large data sets, so holding and joining may hinder performance. Then again, I'm only dealing with a few fields so it shouldn't be all that bad.

------------
WebFOCUS 7.6.2
IBM DB2


WF 7.6.5 / BID / Info Assist / Report Caster - HTML/PDF/EXCEL
 
Posts: 18 | Registered: September 27, 2007Report This Post
Tom Flynn
Expert
posted Hide Post
eb,

"HOPE" it works, although, if there are multiple years involved, it won't.
For that, I would concat the year to the month, or, EDIT(DATE,'999999')...
and use that field as the key for your JOIN...

We ALL work with large volumes of data, it always needs to be presented in an efficient manner.
That's why we create VIEWs, FOCUS DB Files, or separate TABLEs altogether.
Also, is the presentation on a DASHBOARD, ad-hoc query, monthly report, etc.

These are factors everyone deals with when creating a production process.

I'm sure you'll figure out what's best for the client, AND, the most expedient way to present the data...

Good Luck!


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Piipster
Guru
posted Hide Post
How about this in one pass. The LAST keyword allows you to compare the value of a field on the current row to the value of the field on the 'last' row. Since you are sorting by DATE first that should take into account multiple years. When you go from December to January the month is changing and you don't need to test the year. 

 TABLE FILE EXAMPLE
SUM FIELD_1
COMPUTE FDOM/I3 = IF MONTH NE LAST MONTH THEN FIELD_1 ELSE LAST FDOM;
COMPUTE FIELD_2/I3 = FIELD_1 - FDOM;

BY DATE
BY MONTH
END


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders