[CLOSED] Challenge w/ TOTAL and COMPUTE and Rolling Year and Rolling Five Year Change

July 29, 2019, 12:39 PM
Everyone,

Once again, I have a minor challenge with a table containing a rolling year and a rolling five year change.

To summarize the challenge: I need to figure out how to move my State, Other and Grand totals over one column to the left without changing the totals themselves. I think the answer is to create an additional field and somewhow use it for the calculations.

Below I've pasted my code.
   

SET EXCELSERVURL = ''
SET EMGSRV=OFF
SET MESSAGE = OFF
SET ERROROUT = OFF
SET WARNING = OFF
SET TRMOUT = OFF
SET MSG = OFF
SET ASNAMES = ON
SET HOLDATTR = ON
SET EMPTYREPORT = ON
SET ACROSSLINE = OFF
SET NODATA = '-'

DEFINE FILE SALES
STATE/A60V=LCWORD(60, SALES.SALES.STATE_OF_RESIDENCE,STATE);
SALESSET/I4 = EDIT(SALES_YEAR);
YR /YY = SALESSET;
STORE/A66V = STORE_NAME;
END

-SET &MAXYR = &YR;
-SET &MINYR = &MAXYR - 5;

TABLE FILE SALES
SUM
     SALES.SALES.STATE_TOTAL/D20.2 AS 'LASTYR'
BY SALES.SALES.STATE_CODE
BY SALES.SALES.STATE/A60V
BY SALES.SALES.YR
BY SALES.SALES.REGION/A1
BY SALES.SALES.STORE
WHERE SALES.SALES.REGION_NAME EQ '®ION';
WHERE YR GE &MINYR;
WHERE YR LE &MAXYR;
ON TABLE HOLD AS EXTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* EXTRACT MAXIMUM YEAR IN A VARIABLE
-DEFAULTH &YRMAX = 0
TABLE FILE EXTDATA
SUM MAX.YR/I4 AS 'YRMAX'
ON TABLE HOLD AS MAXYR FORMAT BINARY
END
-RUN
-READFILE MAXYR
-SET &YRMAX = &YRMAX;
-TYPE &YRMAX

 EXTRACT ALL INDIVIDUAL YEARS IN A VARIABLE
-DEFAULTH &EXTYR = 0
TABLE FILE EXTDATA
BY YR/I4 AS 'EXTYR'
ON TABLE HOLD AS YRLIST FORMAT BINARY
END
-RUN

-SET &NBYR = &LINES;

-REPEAT READYR FOR &I FROM 1 TO &NBYR STEP 1
-DEFAULTH &YR.&I = 0
-READFILE YRLIST
-SET &YR.&I = &EXTYR;
-TYPE &YR.&I
-READYR

-* CALCULATE INCREASE PCT FOR STATE DETAIL ROWS
TABLE FILE EXTDATA
SUM
STATE_TOTAL/D20.2 AS 'LASTYR'
	COMPUTE LAG_A1 MISSING ON = IF STATE EQ LAST STATE THEN LAST LASTYR ELSE 0;
	COMPUTE LAG_A2 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A1 ELSE 0;
	COMPUTE LAG_A3 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A2 ELSE 0;
	COMPUTE LAG_A4 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A3 ELSE 0;
	COMPUTE LAG_A5 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A4 ELSE 0;
	COMPUTE ONEPCTCHG/P6.2% MISSING ON = IF LAG_A1 GT 0 AND LAST YR GT 0 THEN (STATE_TOT - LAG_A1)/ LAG_A1 * 100 ELSE MISSING;
	COMPUTE FIVEPCTCHG/P6.2% MISSING ON = IF LAG_A5 GT 0 AND STATE_TOT GT 0 THEN (STATE_TOT - LAG_A5)/ LAG_A5 * 100 ELSE MISSING;
WHERE STATE_CODE EQ '1'
BY REGION
BY STORE
BY  TOTAL COMPUTE ROWID/I2 = 1;
BY	STATE/A60V
BY 	YR
ON  TABLE HOLD AS STATEDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR STATE TOTAL ROW
TABLE FILE EXTDATA
SUM
STATE_TOTAL/D20.2 AS 'LASTYR'
	COMPUTE LAG_A1 MISSING ON = IF STATE EQ LAST STATE THEN LAST LASTYR ELSE 0;
	COMPUTE LAG_A2 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A1 ELSE 0;
	COMPUTE LAG_A3 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A2 ELSE 0;
	COMPUTE LAG_A4 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A3 ELSE 0;
	COMPUTE LAG_A5 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A4 ELSE 0;
	COMPUTE ONEPCTCHG/P6.2% MISSING ON = IF LAG_A1 GT 0 AND LAST YR GT 0 THEN (STATE_TOT - LAG_A1)/ LAG_A1 * 100 ELSE MISSING;
	COMPUTE FIVEPCTCHG/P6.2% MISSING ON = IF LAG_A5 GT 0 AND STATE_TOT GT 0 THEN (STATE_TOT - LAG_A5)/ LAG_A5 * 100 ELSE MISSING;
WHERE STATE_CODE EQ '1'
BY REGION
BY STORE
BY TOTAL COMPUTE ROWID/I2 = 2;
BY TOTAL COMPUTE STATE/A60V= 'State Total';
BY 	YR
ON  TABLE HOLD AS STATETDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR COUNTRY DETAILED ROWS
TABLE FILE EXTDATA
SUM
STATE_TOTAL/D20.2 AS 'LASTYR'
	COMPUTE LAG_A1 MISSING ON = IF STATE EQ LAST STATE THEN LAST LASTYR ELSE 0;
	COMPUTE LAG_A2 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A1 ELSE 0;
	COMPUTE LAG_A3 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A2 ELSE 0;
	COMPUTE LAG_A4 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A3 ELSE 0;
	COMPUTE LAG_A5 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A4 ELSE 0;
	COMPUTE ONEPCTCHG/P6.2% MISSING ON = IF LAG_A1 GT 0 AND LAST YR GT 0 THEN (STATE_TOT - LAG_A1)/ LAG_A1 * 100 ELSE MISSING;
	COMPUTE FIVEPCTCHG/P6.2% MISSING ON = IF LAG_A5 GT 0 AND STATE_TOT GT 0 THEN (STATE_TOT - LAG_A5)/ LAG_A5 * 100 ELSE MISSING;
WHERE STATE_CODE NE '1'
BY REGION
BY STORE
BY  TOTAL COMPUTE ROWID/I2 = 3;
BY	STATE/A60V
BY 	YR
ON  TABLE HOLD AS CDETDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR COUNTRY TOTAL ROW
TABLE FILE EXTDATA
SUM
STATE_TOTAL/D20.2 AS 'LASTYR'
	COMPUTE LAG_A1 MISSING ON = IF STATE EQ LAST STATE THEN LAST LASTYR ELSE 0;
	COMPUTE LAG_A2 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A1 ELSE 0;
	COMPUTE LAG_A3 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A2 ELSE 0;
	COMPUTE LAG_A4 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A3 ELSE 0;
	COMPUTE LAG_A5 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A4 ELSE 0;
	COMPUTE ONEPCTCHG/P6.2% MISSING ON = IF LAG_A1 GT 0 AND LAST YR GT 0 THEN (STATE_TOT - LAG_A1)/ LAG_A1 * 100 ELSE MISSING;
	COMPUTE FIVEPCTCHG/P6.2% MISSING ON = IF LAG_A5 GT 0 AND STATE_TOT GT 0 THEN (STATE_TOT - LAG_A5)/ LAG_A5 * 100 ELSE MISSING;
WHERE STATE_CODE NE '1'
BY REGION/A1
BY STORE
BY	TOTAL COMPUTE STATE/A60V = 'Other Country Total';
BY  TOTAL COMPUTE ROWID/I2 = 4;
BY 	YR
ON  TABLE HOLD AS CTOTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

-* CALCULATE INCREASE PCT FOR GRAND TOTAL ROW
TABLE FILE EXTDATA
SUM
STATE_TOTAL/D20.2 AS 'LASTYR'
	COMPUTE LAG_A1 MISSING ON = IF STATE EQ LAST STATE THEN LAST LASTYR ELSE 0;
	COMPUTE LAG_A2 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A1 ELSE 0;
	COMPUTE LAG_A3 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A2 ELSE 0;
	COMPUTE LAG_A4 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A3 ELSE 0;
	COMPUTE LAG_A5 MISSING ON = IF STATE EQ LAST STATE THEN LAST LAG_A4 ELSE 0;
	COMPUTE ONEPCTCHG/P6.2% MISSING ON = IF LAG_A1 GT 0 AND LAST YR GT 0 THEN (STATE_TOT - LAG_A1)/ LAG_A1 * 100 ELSE MISSING;
	COMPUTE FIVEPCTCHG/P6.2% MISSING ON = IF LAG_A5 GT 0 AND STATE_TOT GT 0 THEN (STATE_TOT - LAG_A5)/ LAG_A5 * 100 ELSE MISSING;
BY REGION
BY STORE
BY TOTAL COMPUTE STATE/A60V='Total';
BY TOTAL COMPUTE ROWID/I2 = 5;
BY YR
ON  TABLE HOLD AS TOTDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
END
-RUN

TABLE FILE STATEDATA
PRINT
	LASTYR
	LAG_A1
	LAG_A2
	LAG_A3
	LAG_A4
	LAG_A5
	ONEPCTCHG
	FIVEPCTCHG
BY REGION
BY STORE
BY ROWID
BY STATE
BY YR
ON TABLE HOLD AS FINALDATA FORMAT FOCUS
-*ON TABLE PCHOLD FORMAT HTML
MORE
FILE STATETDATA
MORE
FILE CDETDATA
MORE
FILE CTOTDATA
MORE
FILE TOTDATA
END
-RUN
-GOTO LBL_PRINT;

-*****PRINT SECTION BEGINS HERE ******
-LBL_PRINT

-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0, &YR4 = 0, &YR5 = 0, &YR6 = 0;

DEFINE FILE FINALDATA
COL1/D12C MISSING ON = IF YR EQ &YR1.EVAL THEN LASTYR ELSE MISSING;
COL2/D12C MISSING ON = IF YR EQ &YR2.EVAL THEN LASTYR ELSE MISSING;
COL3/D12C MISSING ON = IF YR EQ &YR3.EVAL THEN LASTYR ELSE MISSING;
COL4/D12C MISSING ON = IF YR EQ &YR4.EVAL THEN LASTYR ELSE MISSING;
COL5/D12C MISSING ON = IF YR EQ &YR5.EVAL THEN LASTYR ELSE MISSING;
COL6/D12C MISSING ON = IF YR EQ &YR6.EVAL THEN LASTYR ELSE MISSING;
COL7/P6.2C% MISSING ON = IF YR EQ &YR6.EVAL THEN ONEPCTCHG ELSE MISSING;
COL8/P6.2C% MISSING ON = IF YR EQ &YR6.EVAL THEN FIVEPCTCHG ELSE MISSING;
END

TABLE FILE FINALDATA
SUM
	COL1 AS '&YR1.EVAL'
	COL2 AS '&YR2.EVAL'
	COL3 AS '&YR3.EVAL'
	COL4 AS '&YR4.EVAL'
	COL5 AS '&YR5.EVAL'
	COL6 AS '&YR6.EVAL'
	COL7 AS '1 Year Chg'
	COL8 AS '5 Year Chg'
BY REGION NOPRINT
BY STORE AS 'Store'
BY ROWID NOPRINT
BY STATE AS 'STATE'
ON STORE SUBFOOT " "
ON TABLE SUBHEAD
"Julie's Chain of Stores"
"Sales by State"
"Sales Years &MINYR - &MAXYR"
"&Region Listing"
FOOTING
"Source: Sales Data"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML Web Document (HTML),HTML>,<Excel XLSX (XLSX),XLSX>).Output type.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='WebFOCUS Report', ORIENTATION=LANDSCAPE, $
TYPE=FOOTING, LINE=1, ITEM=1, OBJECT=TEXT, JUSTIFY=RIGHT, FONT='Calibri', SIZE=9, COLOR=RGB(0 0 0), STYLE=NORMAL, $
$
TYPE=ACROSSVALUE,
     JUSTIFY=RIGHT,
$
TYPE=DATA,
COLUMN = N10,
BORDER-RIGHT=LIGHT,
BORDER-RIGHT-COLOR=RGB(0 58 99),
$
TYPE=REPORT,
	 HFREEZE=TOP,
	 SQUEEZE=ON,
$
TYPE=TITLE,
	 BORDER-BOTTOM=ON,
	 BORDER-BOTTOM-COLOR=RGB (250 250 250),
	 JUSTIFY=RIGHT,
$
TYPE=TITLE,
	 COLUMN=N2,
     JUSTIFY=LEFT,
	 BORDER-BOTTOM=ON,
	 BORDER-BOTTOM-COLOR=RGB (250 250 250),
	 WIDTH=0.2,
$
TYPE=DATA,
	 COLUMN=N2,
     JUSTIFY=LEFT,

$
TYPE=TITLE,
	 COLUMN=N4,
     JUSTIFY=LEFT,
	 BORDER-BOTTOM=ON,
	 BORDER-BOTTOM-COLOR=RGB (250 250 250),
	 WIDTH=0.2,
$
TYPE=DATA,
	 COLUMN=N4,
     JUSTIFY=LEFT,
$
TYPE=TITLE,
	 COLUMN=N8,
     JUSTIFY=RIGHT,
	 BORDER-BOTTOM=ON,
	 BORDER-BOTTOM-COLOR=RGB (250 250 250),
$
TYPE=TITLE,
	 COLUMN=N11,
     JUSTIFY=RIGHT,
	 BORDER-BOTTOM=ON,
	 BORDER-BOTTOM-COLOR=RGB (250 250 250),
$
TYPE=ACROSSVALUE,
     JUSTIFY=RIGHT,
$
TYPE=ACROSSVALUE,
	 ACROSS=1,
	 WRAP=ON,
	 WIDTH=0.5,
	 GRID=ON,
$
TYPE=DATA,
     JUSTIFY=RIGHT,
$
TYPE=DATA,
 	 BORDER-TOP=OFF,
	 BORDER-BOTTOM=OFF,
$
TYPE=DATA,        WHEN = STATE EQ 'Kansas Total', BORDER-TOP=LIGHT, BORDER-TOP-COLOR=RGB(0 58 99), $
TYPE=DATA,        WHEN = STATE EQ 'Kansas Total', BORDER-BOTTOM=LIGHT, BORDER-BOTTOM-COLOR=RGB(0 58 99),$
TYPE=DATA,        WHEN = STATE EQ 'Kansas Total', BACKCOLOR=RGB(250 250 250), STYLE=NORMAL, $

TYPE=DATA,        WHEN = STATE EQ 'Other Total', BORDER-TOP=LIGHT, BORDER-TOP-COLOR=RGB(0 58 99), $
TYPE=DATA,        WHEN = STATE EQ 'Other Total', BORDER-BOTTOM=LIGHT, BORDER-BOTTOM-COLOR=RGB(0 58 99),$
TYPE=DATA,        WHEN = STATE EQ 'Other Total', BACKCOLOR=RGB(250 250 250), STYLE=NORMAL, $

-*TYPE=DATA,        WHEN = STATE EQ 'Total', BORDER-TOP=LIGHT, BORDER-TOP-COLOR=RGB(0 58 99), $
-*TYPE=DATA,        WHEN = STATE EQ 'Total', BORDER-BOTTOM=LIGHT, BORDER-BOTTOM-COLOR=RGB(0 58 99),$
TYPE=DATA,        WHEN = STATE EQ 'Total', BACKCOLOR=RGB(0 58 99), STYLE=NORMAL, $
TYPE=DATA,        WHEN = STATE EQ 'Total', COLOR=RGB(255 226 146), STYLE=NORMAL, $

ENDSTYLE
END
-RUN
Right now my totals appear in the county column, and when I try to recompute in the institution column, the calculation is not correct.

Does anyone have any thoughts? As I said, I think I need to create a new field for sorting and total purposes.
This message has been edited. Last edited by: FP Mod Chuck,