[SOLVED] SUM first 10 years data and combine the rest years to one category

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, 2005

Ignored post by j.gross posted

susannah

Expert

posted

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, 2003

Ignored post by susannah posted

Gold member

posted

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, 2015

Ignored post by WebFocusDiver posted

susannah

Expert

posted

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, 2003

Ignored post by susannah posted

Gold member

posted

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, 2015

Ignored post by WebFocusDiver posted

j.gross

Virtuoso

posted

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

- Jack Gross
WF through 8.1.05

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Gold member

posted

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, 2015

Ignored post by WebFocusDiver posted

j.gross

Virtuoso

posted

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, 2005

Ignored post by j.gross posted

Gold member

posted

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, 2015

Ignored post by WebFocusDiver posted