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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SUM first 10 years data and combine the rest years to one category

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SUM first 10 years data and combine the rest years to one category
 Login/Join
 
Gold member
posted
I am trying to graph the payment of first 10 years and use "PLUS OTHERS AS XXXXXX". I define a field in master data to extract year from a date field. However I tried different ways but this "Others" category will show as "yyyyyyyy.." in my chart. Does anyone know how to fix it?


Thanks

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 60 | Registered: March 02, 2015Report This Post
Virtuoso
posted Hide Post
If you know a priori that the first year is, say, 2015, you can DEFINE

Year/I4 = ...;
sort_year/A5 = IF Year LT 2025 THEN EDIT(Year) ELSE '2025+';

and use

SUM ...
BY sort_year as 'Year'


If the start year varies, you'll need to replace "2025" with references to an amper var.

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
make a separate record
here's a simple example
TABLE FILE CAR
SUM SALES BY COUNTRY
ON TABLE HOLD AS H1
END
-RUN

DEFINE FILE CAR
COUNTRY/A10 WITH COUNTRY=' EUROPE';
END
TABLE FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS H2
END
-RUN

TABLE FILE H1
SUM SALES BY COUNTRY
ON TABLE HOLD AS H3
MORE
FILE H2
END
-RUN
GRAPH FILE H3 SUM SALES BY COUNTRY
END




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Gold member
posted Hide Post
I am not sure what do you mean by separate record. Do you mean sum the first 10 years record into H1 and Sum the rest as separate file and combine them together? Thanks


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 60 | Registered: March 02, 2015Report This Post
Expert
posted Hide Post
h2 in this case contains the aggregate.
yes, run the code snippet and see what happens.
if you need a single record that is the aggregate or some other combination of a bunch of others, just make a new record and append it to your granular ones. easy.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Gold member
posted Hide Post
quote:
Originally posted by j.gross:
If you know a priori that the first year is, say, 2015, you can DEFINE

Year/I4 = ...;
sort_year/A5 = IF Year LT 2025 THEN EDIT(Year) ELSE '2025+';

and use

SUM ...
BY sort_year as 'Year'


If the start year varies, you'll need to replace "2025" with references to an amper var.


Where should I create the amper variable? The date is a input field. I use
-DEFAULT &EVDATE='2014-01-31'
then would like to find the year of 10 years from this date.

Thanks


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 60 | Registered: March 02, 2015Report This Post
Virtuoso
posted Hide Post
Place a -SET statement (or maybe a series of them) right after that -DEFAULT line, to compute the ten-year-out date, in the format you will need, based on the received value of &EVDATE.

I take it you are defining a "year" as the calendar year, so only the Year portion of the input date matters. Then
-SET &EVYEAR1=EDIT(&EVDATE,'9999');
-SET &EVYEAR10=&EVYEAR1+9;

will do it.

...as long as the received &EVDATE value is guaranteed to conform to the required yyyy-mm-dd format Wink .


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
Use my example, if the month of &EVDATE is 12, then the &EVYEAR1 should be 2015. Can I use IF THEN ELSE statement within -SET? Also how do i find out the format of the &EVDATE? And how do I see what is the value of &EVYEAR10 after -SET?

Thank you very mcuh


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 60 | Registered: March 02, 2015Report This Post
Virtuoso
posted Hide Post
You need to be more specific about your business rules. Is "year" always going to just reflect the year portion of each data record's date value -- or can the effective "year" start on an arbitrary month (e.g., April thru March)?

  • If the former, what month-values would use the incoming year as-is for Year 1, and which would increment it?

  • If the latter, how is the year defined for year 1,2,...; and what will the assigned "year" value be, for purposes of sorting, summing, and labelling? -- Your DEFINE and WHERE statements will have to reflect the business rule for defining the "year" index of each incoming record.


The incoming date parameter will be a text string (all amper vars are typeless character strings), which you have to manipulate to obtain the magic constants that enter the DEFINE and WHERE clauses. -- There are lots of text-manipulation and date-manipulation supplied functions at your disposal for use in -SET. Consult the Help files, and then search FocalPoint for examples of their use in -SET.

Bonne chance!


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
Finally got it to work. Thank you for your help. Here is the code.
-DEFAULT &EVDATE='2014-01-31'

-SET &EVYEAR1= EDIT(DATECVT(&EVDATE,'I8YYMD','I8YYMD'),'9999');
-SET &EVYEAR10=&EVYEAR1+9;
DEFINE FILE CASHFLOW
YEAR/I4=EDIT(SUBSTRING(CASHFLOW.CFDATE,1,4));
SUMMARYYEAR/A20V = IF YEAR LE &EVYEAR10 THEN CF ELSE 'Beyond 10 Years';
END


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 60 | Registered: March 02, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SUM first 10 years data and combine the rest years to one category

Copyright © 1996-2020 Information Builders