Hello All,

I am reworking a few reports using an across selecting data from the previous and current years in one query for performance. (We were doing this with two hold files, then a match then another table file.) I am having an issue doing a compute in the table file call because I do not know how to uniquely identify the fields, (i.e. current year vs. previous year) to do a compute. A sample compute would be COMPUTE SALES_WEIGHT_CHG /P13C = SALES_WEIGHT - SALES_WEIGHT ; AS '';. The first sales weight field is the first field displayed and the second sales weight field is the second field displayed. I tried this: COMPUTE SALES_WEIGHT_CHG /P13C = C1 - C2; This does not work either because this computed field is calculated twice per row because of the across.
----
Here is the code:

SET ASNAMES = ON
SET NODATA = 0
JOIN
OUTLET_ITEM.OUTLET IN OUTLET_ITEM TO OUTLET_RECAP.OUTLET IN OUTLET_RECAP AS J0
END
JOIN
OUTLET_ITEM.COMMODITY_NUMBER IN OUTLET_ITEM TO COMMODITY.COMMODITY_NUMBER
IN COMMODITY AS J1
END
DEFINE FILE OUTLET_ITEM
OUTLET_A/A11=EDIT(OUTLET, '$$$$$999999');
COMMODITY_NUMBER_A/A6=EDIT(COMMODITY_NUMBER, '$$$999');
R2HEADERA/A200='Outlet Recap for ' | OUTLET_A | ' - ' | NAME;
R2HEADERB/A200='Commodity: ' | COMMODITY_NUMBER_A | ' - ' | DESCRIPTION;
END
TABLE FILE OUTLET_ITEM
SUM
SALES_WEIGHT AS 'Weight' OVER
QUANTITY_SHIPPED AS 'Shipped Cases' OVER
GROSS_SALES AS 'Gross Sales' OVER
CWT_CLAIMS AS 'Claims' OVER
CWT_CASH_DISCOUNT AS 'Cash Discount' OVER
ADJ_GROSS_SALES AS 'Adj Gross Sales' OVER
DEF_PROMO_AMT_OFF_INV AS 'OI' OVER
DEF_PROMO_AMT_BY_CHECK AS 'BB' OVER
X_FREIGHT_ALLOW AS 'Freight Allow' OVER
X_MISC_ALLOW AS 'Misc Allow' OVER
COMPUTE TOTAL_ALLOW/D12C = DEF_PROMO_AMT_OFF_INV + DEF_PROMO_AMT_BY_CHECK + X_MISC_ALLOW;
AS 'Total Allow' OVER
SALES_B4_PROMO AS 'Sales Before Promo' OVER
DEF_TOTAL_ACCRUAL AS 'Accrued Promo' OVER
SALES_AMOUNT AS 'Net Sales' OVER
CWT_SALES_AMOUNT AS 'Net Sales /CWT' OVER
CSE_SALES_AMOUNT AS 'Net Sales /CSE' OVER
SALES_MARGIN AS 'Gross Margin' OVER
CWT_SALES_MARGIN AS 'Gross Margin /CWT' OVER
CSE_SALES_MARGIN AS 'Gross Margin /CSE' OVER
SERVICE_LEVEL AS 'Service Level' OVER
QUANTITY_ORDERED OVER
QUANTITY_SHIPPED OVER
SALES_STD_CASES OVER
FREIGHT_AMOUNT
BY
HIGHEST LEVEL NOPRINT
BY
OUTLET AS ' '
BY
NAME AS ' '
ACROSS
HIGHEST Year NOPRINT
ON OUTLET SKIP-LINE
ON OUTLET PAGE-BREAK
ON NAME SUBHEAD
" [+0] [+0] MEASURE [+0]CURRENT [+0]PREVIOUS [+0]CHANGE [+0]INDEX"
HEADING
"[R2HEADERA"
"[R2HEADERB"
" "
WHERE (( OUTLET EQ 991999 )
OR (OUTLET_RECAP EQ 991999))
AND ( COMMODITY_NUMBER EQ 459 )
AND (( FISCAL_YEARWEEK GE 200201 )
AND ( FISCAL_YEARWEEK LE 200202)
OR ( FISCAL_YEARWEEK GE 200101 )
AND ( FISCAL_YEARWEEK LE 200102 ));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE OFF
END
-* I did not put the style sheet in. It is not relevant here.
----
So, any solutions to this? I don't know if this is possible, but I would like to try an if statement that says if we get to the second column of the row, display the compute, if not, don't. Maybe that is not the route to try. Any suggestions? Thanks for your help!

-FOCUS Freak

Here is the suggestion from FOCWIZARD, Art Greenhaus:

The user has the right idea, but the wrong count. Every verb object counts as a column. So, SALES_WEIGHT is column 1 (C1), QUANTITY_SHIPPED is column 2 (C2), all the way through FREIGHT_AMOUNT which is column 24 (C24), assuming I've counted correctly. Note, each COMPUTE is counted, just like any other verb object. Even NOPRINTed fields are counted. One subtlety to consider, if you reference a field in a COMPUTE, which was NOT previously mentioned as a verb object, we INSERT the field as a NOPRINTed field, before the COMPUTE, so we can evaluate it. Thus, if I have:

SUM SEATS AND COMPUTE PROFIT=RCOST-DCOST;

Then PROFIT is C4, as RCOST is C2 and DCOST is C3 (NOPRINTed).

Note that whether the verb objects are connected with AND (by default) or OVER makes no difference.

Now, when you have an ACROSS, the verb objects repeat for each ACROSS value, so the verb objects for the SECOND ACROSS field start at C25 and go up. Thus, the correct calculation is:

COMPUTE SALES_WEIGHT_CHG /P13C = C1 - C25;

One further point is that this COMPUTE must be specified AFTER the ACROSS, so the COMPUTED field does NOT get treated as a verb object, but is done 'outside' the ACROSS values.