Hi,

I have the below data set with first two columns.

  
Year Product   Curr Count  Prev Count  Diff

2011	1	50		0	50
	2	40		0	40
	3	500		0	500  
2012	1	20		50	-30
	2	200		40	160
	3	600		500	100      	
2013	1	100		20	80
	2	50		200	-150
	3	700		600	100      
2014	1	400		100	300
	2	200		50	150
	3	850		700	150	       
2015	1	150		400	-250
	2	300		200	100
	3	1050		850    	200

I have Year and Curr Count columns and i am trying to calculate the rest of the column.
It looks very simple and i have done something similar using

LAST

command.But now i am not able to bring the Prev Count column.Once i have Prev Count i can calculate diff.But i am stuck in logic to calculate Prev Count.

Any help would be appreciated.

This message has been edited. Last edited by: srajeevan,

You can achieve the desired result by doing it through an intermediate file.
First extract the data, sort it by product and year - which will allow your calculations to be performed correctly. Then hold the result and report off that hold file. The simplified example for this would be something like:

TABLE FILE xyzzy
SUM   CNTR
      COMPUTE PCNTR/I6 = IF PROD NE LAST PROD THEN 0 ELSE LAST CNTR;
      COMPUTE DIFF/I6  = CNTR - PCNTR;
BY PROD
BY YEAR
ON TABLE HOLD
END

TABLE FILE HOLD
PRINT CNTR   AS 'Curr Count'
      PCNTR  AS 'Prev Count'
      DIFF   AS 'Diff'
BY    YEAR   AS 'Year'
BY    PROD   AS 'Product'
END

Hope this helps...

Have you looked at the PARTITION_AGGR and PARTITION_REF functions?

@GamP : I was able to use your solution.Thanks.
@jgelona : I will try to use the functions you mentioned and see if it can help.Thanks.