Hello FocalPoint Community,

I have what I hope is a simple question.

I'd like to apply a SET NODATA character to two specific columns-those of the 1 Yr Pct Chg and the 5 Yr Pct Chg in the final output.

Is there a way to do that other than writing something like: IF THIS THEN THIS ELSE IF THIS THEN '-'?

Please see COL7 and COL8 below. I've looked at a lot of manuals this morning, and I know I'm overlooking something.

I asked because I tried it.

DEFINE FILE GROCERY_SALES
YR/I4=EDIT(SALES_YEAR);
SORT_STORE/A66V = STORE_NAME;
END

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

TABLE FILE GROCERY_SALES
SUM
    GROCERT_SALES.GROCERT_SALES.UNITS_SOLD AS 'SC_COUNTS'
BY  GROCERY_SALES.GROCERY_SALES.REGION_NO
BY  GROCERY_SALES.GROCERY_SALES.REGION_NAME
BY  GROCERY_SALES.GROCERY_SALES.STORE_NAME
BY  GROCERY_SALES.GROCERY_SALES.YR
BY 	SORT_STORE/A66V
WHERE (GROCERY_SALES.GROCERY_SALES.YR GE &MINYR);
WHERE (GROCERY_SALES.GROCERY_SALES.YR LE &MAXYR);
WHERE GROCERY_SALES.GROCERY_SALES.SALES_QUARTER EQ &SALES_QUARTER.(FIND GROCERY_SALES.GROCERY_SALES.SALES_QUARTER IN GROCERY_SALES|FORMAT=A13V).SALES_QUARTER:.QUOTEDSTRING;
ON TABLE HOLD AS EXTDATA
END
-RUN
-GOTO LBL_PRINT;

*There are two other tables users can select in a dropdown menu. I chose not to include them here. The code is the same.

-LBL_PRINT
-* 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

TABLE FILE EXTDATA
SUM
	SC_COUNTS
	COMPUTE LAG_A1 MISSING ON = IF SORT_INST EQ LAST SORT_STORE THEN LAST SC_COUNTS ELSE 0;
	COMPUTE LAG_A2 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A1 ELSE 0;
	COMPUTE LAG_A3 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A2 ELSE 0;
	COMPUTE LAG_A4 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A3 ELSE 0;
	COMPUTE LAG_A5 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A4 ELSE 0;
	COMPUTE PCTCHG1/D11.1%  MISSING ON = IF LAG_A1 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A1) / LAG_A1 *100 ELSE MISSING;
	COMPUTE PCTCHG5/D11.1%  MISSING ON = IF LAG_A5 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A5) / LAG_A5 *100 ELSE MISSING;
BY  TOTAL COMPUTE ROWID/I2 = 1;
BY	REGION_NO/A3V
BY  REGION_NAME/A20V
BY	SORT_STORE/A66V
BY 	YR
ON  TABLE HOLD AS INSTDATA
END
-RUN
-*-EXIT

TABLE FILE EXTDATA
SUM
	SC_COUNTS
	COMPUTE LAG_A1 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST SC_COUNTS ELSE 0;
	COMPUTE LAG_A2 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A1 ELSE 0;
	COMPUTE LAG_A3 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A2 ELSE 0;
	COMPUTE LAG_A4 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A3 ELSE 0;
	COMPUTE LAG_A5 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A4 ELSE 0;
	COMPUTE PCTCHG1/D11.1%  MISSING ON = IF LAG_A1 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A1) / LAG_A1 *100 ELSE MISSING;
	COMPUTE PCTCHG5/D11.1%  MISSING ON = IF LAG_A5 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A5) / LAG_A5 *100 ELSE MISSING;
BY  TOTAL COMPUTE ROWID/I2 = 2;
BY	REGION_NO/A3V
BY  TOTAL COMPUTE REGION_NAME/A20V= 'Total';
BY  TOTAL COMPUTE SORT_STORE/A66V = ' ';
BY 	YR
ON  TABLE HOLD AS SUBDATA
END
-RUN
-*-EXIT

TABLE FILE EXTDATA
SUM
	SC_COUNTS
	COMPUTE LAG_A1 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST SC_COUNTS ELSE 0;
	COMPUTE LAG_A2 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A1 ELSE 0;
	COMPUTE LAG_A3 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A2 ELSE 0;
	COMPUTE LAG_A4 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A3 ELSE 0;
	COMPUTE LAG_A5 MISSING ON = IF SORT_STORE EQ LAST SORT_STORE THEN LAST LAG_A4 ELSE 0;
	COMPUTE PCTCHG1/D11.1%  MISSING ON = IF LAG_A1 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A1) / LAG_A1 *100 ELSE MISSING;
	COMPUTE PCTCHG5/D11.1%  MISSING ON = IF LAG_A5 GT 0 AND SC_COUNTS GT 0 THEN (SC_COUNTS - LAG_A5) / LAG_A5 *100 ELSE MISSING;
BY  TOTAL COMPUTE ROWID/I2 = 3;
BY	TOTAL COMPUTE REGION_NO/A3V = '5';
BY  TOTAL COMPUTE REGION_NAME/A20V='System Total';
BY  TOTAL COMPUTE SORT_STORE/A66V = ' ';
BY 	YR
ON  TABLE HOLD AS TOTDATA
END
-RUN

TABLE FILE INSTDATA
PRINT
	SC_COUNTS
	LAG_A1
	LAG_A2
	LAG_A3
	LAG_A4
	LAG_A5
    PCTCHG1
    PCTCHG5
BY  ROWID
BY 	REGION_NO
BY  REGION_NAME
BY 	SORT_STORE
BY 	YR
ON TABLE HOLD AS FINAL
MORE
FILE SUBDATA
MORE
FILE TOTDATA
END
-RUN

-DEFAULTH &YR1 = 0, &YR2 = 0, &YR3 = 0, &YR4 = 0, &YR5 = 0, &YR6 = 0;
DEFINE FILE FINAL
COL1/D12C MISSING ON = IF YR EQ &YR1.EVAL THEN SC_COUNTS ELSE MISSING;
COL2/D12C MISSING ON = IF YR EQ &YR2.EVAL THEN SC_COUNTS ELSE MISSING;
COL3/D12C MISSING ON = IF YR EQ &YR3.EVAL THEN SC_COUNTS ELSE MISSING;
COL4/D12C MISSING ON = IF YR EQ &YR4.EVAL THEN SC_COUNTS ELSE MISSING;
COL5/D12C MISSING ON = IF YR EQ &YR5.EVAL THEN SC_COUNTS ELSE MISSING;
COL6/D12C MISSING ON = IF YR EQ &YR6.EVAL THEN SC_COUNTS ELSE MISSING;

COL7/P6.1C% MISSING ON  = IF YR EQ &YR6.EVAL THEN PCTCHG1 ELSE MISSING;
COL8/P6.1C% MISSING ON  = IF YR EQ &YR6.EVAL THEN PCTCHG5 ELSE MISSING;

END

-SET &RPT_HEAD2 = IF &SALES_QUARTER EQ 'Sales Years' THEN 'Sales Years' ELSE &SALES_QUARTER;

TABLE FILE FINAL
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'

	PCTCHG1 AS '1 Year Chg'
	PCTCHG5 AS '5 Year Chg'

BY  REGION_NO NOPRINT
BY  ROWID NOPRINT
BY  REGION_NAME AS 'Region'
BY  SORT_STORE/A66V AS 'Store'
ON REGION_NO SUBFOOT " "
ON TABLE SUBHEAD
"Grocery Stores International"
"Sales Summary by &RPT_HEAD"
"&RPT_HEAD2 &MINYR - &MAXYR"
"All Stores"
FOOTING
"Source: Sales Records"
"&RPT_FOOT"
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 *
  PE=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=FOOTING, JUSTIFY=RIGHT, FONT='Calibri', SIZE=9, COLOR=RGB(0 0 0), STYLE=ITALIC,
$
TYPE=TITLE,
	 BORDER-BOTTOM=ON,
	 BORDER-BOTTOM-COLOR=RGB (250 250 250),
	 JUSTIFY=RIGHT,
$
TYPE=TITLE,
	 COLUMN=N3,
 NDSTYLE
END
-RUN
-LBL_END;
  

This message has been edited. Last edited by: FP Mod Chuck,

Julie

The NODATA is all or nothing, you will have to use a DEFINE or COMPUTE on those columns to get the results you want.