[SOLVED] Operating with a rusty brain! Need help with YTD accumulation

September 19, 2011, 07:51 PM

Francis Mariani

I'm just back on the wagon and my brain is rusty.

I'm trying to create a YTD field in a HOLD file and I just can't figure it out. It's got to be simple, but it's been six weeks since I had one thought about WebFOCUS.

This is my test fex:

-SET &ECHO=ALL;

SET BYDISPLAY=ON
SET HOLDFORMAT=ALPHA
SET HOLDLIST=PRINTONLY
-RUN

-*-- Create the master for TEST -------------------------
APP FI DATAMAST DISK test.mas
-RUN

-WRITE DATAMAST FILE=TEST, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=DATE1   , ALIAS=E01, USAGE=YYMD, ACTUAL=A08, $
-WRITE DATAMAST   FIELD=        , ALIAS=E02, USAGE=A01 , ACTUAL=A01, $
-WRITE DATAMAST   FIELD=AMT     , ALIAS=E03, USAGE=I02 , ACTUAL=A02, $

-*-- Create the data file for TEST ----------------------
FILEDEF TEST DISK test.ftm
-RUN

-WRITE TEST 20101221 15
-WRITE TEST 20101222 05
-WRITE TEST 20101223 20
-WRITE TEST 20101224 35
-WRITE TEST 20101225 15

-WRITE TEST 20110101 10
-WRITE TEST 20110102 20
-WRITE TEST 20110103 10
-WRITE TEST 20110104 05
-WRITE TEST 20110105 10

DEFINE FILE TEST
DATE1_BEG/YYMD = DATEMOV(DATE1, 'BOY');
AMT_YTD/D6 = 0;
END
-RUN

TABLE FILE TEST
SUM 
AMT_YTD
BY DATE1_BEG NOPRINT

PRINT
AMT

BY DATE1_BEG NOPRINT
BY DATE1
END
-RUN

What I'd like to get is the following:

DATE1    AMT AMT_YTD
-------- --- -------
20101221  15      15
20101222  05      20
20101223  20      40
20101224  35      75
20101225  15      90
20110101  10      10
20110102  20      30
20110103  10      40
20110104  05      45
20110105  10      55

I am missing something essential.

Thanks,

This message has been edited. Last edited by: Francis Mariani,

September 19, 2011, 08:05 PM

Waz

A little hint....

Try LAST in a calc.

September 19, 2011, 08:40 PM

Francis Mariani

thanks!!!

-SET &ECHO=ALL;

SET BYDISPLAY=ON
SET HOLDFORMAT=ALPHA
SET HOLDLIST=PRINTONLY
-RUN

-*-- Create the master for TEST -------------------------
APP FI DATAMAST DISK test.mas
-RUN

-WRITE DATAMAST FILE=TEST, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=DATE1   , ALIAS=E01, USAGE=YYMD, ACTUAL=A08, $
-WRITE DATAMAST   FIELD=        , ALIAS=E02, USAGE=A01 , ACTUAL=A01, $
-WRITE DATAMAST   FIELD=AMT     , ALIAS=E03, USAGE=I02 , ACTUAL=A02, $

-*-- Create the data file for TEST ----------------------
FILEDEF TEST DISK test.ftm
-RUN

-WRITE TEST 20101221 15
-WRITE TEST 20101222 05
-WRITE TEST 20101223 20
-WRITE TEST 20101224 35
-WRITE TEST 20101225 15

-WRITE TEST 20110101 10
-WRITE TEST 20110102 20
-WRITE TEST 20110103 10
-WRITE TEST 20110104 05
-WRITE TEST 20110105 10

DEFINE FILE TEST
DATE1_BOY/YYMD = DATEMOV(DATE1, 'BOY');
AMT_YTD1/D13 = IF DATE1_BOY EQ LAST DATE1_BOY THEN AMT + LAST AMT_YTD1 ELSE AMT;
END
-RUN

TABLE FILE TEST
PRINT
DATE1_BOY

AMT
AMT_YTD

BY DATE1

ON TABLE HOLD AS H001
END
-RUN

TABLE FILE H001
PRINT *
END
-RUN

My brain is slowly leaving the station!

September 19, 2011, 08:48 PM

Waz

Stoke that coal....

September 20, 2011, 07:30 AM

<JJI>

Francis,

Here is another possible solution without a hold file.

DEFINE FILE TEST
DATE1_BEG/YYMD = DATEMOV(DATE1, 'BOY');
AMT_YTD/D6 = 0;
END
-RUN

TABLE FILE TEST
SUM
 AMT
COMPUTE CMP_AMT_YTD/D9  = IF DATE1_BEG EQ LAST DATE1_BEG THEN LAST CMP_AMT_YTD + AMT ELSE AMT;
BY DATE1_BEG NOPRINT
BY DATE1
END
-RUN

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

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

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