Hi i am trying to achieve the report output in below format.

  
			        2016	2017    Diff
Product1			167	265	98
Product2			3,053	3,083	30
Product3			4	7	3
product4			63	51	-12
product5			67	117	50
product6			591	245	-346

I am unable to create a define field for finding difference as the sales count field is coming as two columns across years.
How can i achieve this.

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

This is one option, other such as MacGyver technique may also be applicable

DEFINE FILE GGSALES
YR /YY    = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS AS 'VAL'
BY CITY
BY YR
BY TOTAL COMPUTE ROWLABEL/A10 = EDIT(YR);
ON TABLE HOLD AS EXTDATA
END
-RUN

DEFINE FILE EXTDATA
SALES1 /D6 = IF YR EQ 1996 THEN VAL ELSE 0;
SALES2 /D6 = IF YR EQ 1997 THEN VAL ELSE 0;
END
TABLE FILE EXTDATA
SUM COMPUTE VAL/I08 = (SALES2 - SALES1);
BY CITY
BY TOTAL COMPUTE YR/YY = 9999;
BY TOTAL COMPUTE ROWLABEL/A10 = 'Diff';
ON TABLE HOLD AS DIFFDATA
END
-RUN

TABLE FILE EXTDATA
SUM VAL AS ''
BY CITY AS ''
ACROSS YR NOPRINT
ACROSS ROWLABEL AS ''
MORE
FILE DIFFDATA
END
-RUN

If that is indeed all you need, a simple table request will suffice.

TABLE FILE srajeevan
SUM VALUE AS ''
 BY PRODUCT AS ''
ACROSS YEAR AS ''
COMPUTE Diff/D20 = C2 - C1;
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  grid=off, $
ENDSTYLE
END

Using data built from the following. I can only assume that this is similar structure to your data.

EX -LINES * EDAPUT MASTER,srajeevan,CV,FILE
FILENAME=srajeevan, SUFFIX=FOCUS
SEGNAME=SEG01, $
FIELDNAME=PRODUCT, ,A20    ,A20  ,$
FIELDNAME=YEAR,    ,I4     ,I4   ,$
FIELDNAME=VALUE,   ,D20  ,A20  ,$
EDAPUT*
-RUN
CREATE FILE srajeevan
MODIFY FILE srajeevan
FREEFORM PRODUCT YEAR VALUE
DATA
Product1,2016,167,$
Product1,2017,265,$
Product2,2016,3053,$
Product2,2017,3083,$
Product3,2016,4,$
Product3,2017,7,$
product4,2016,63,$
product4,2017,51,$
product5,2016,67,$
product5,2017,117,$
product6,2016,591,$
product6,2017,245,$
END

T

Tony