$K	Year	Apr-09	May-09 Jun-09	Jul-09	Aug-09	Sep-09	Oct-09	Nov-09	Dec-09	Jan-10	Feb-10	Mar-10	Total
Shipment  2009	424	559	560	537	451	606	588	392	0	0	0	0	4,116
	2008	741	764	818	758	776	833	682	794	516	506	497	597	8,281
YOY		-317	-205	-258										
 

Is there a way to calculate difference between two rows ?

Thank you.
KK

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

Hi KK,

You can use the LAST command.

Best Regards,

Jimmy Pang

That's the best bang for the buck: The LAST Command.

Here's a simple code used in a COMPUTE as an example:

  
TABLE FILE CAR
SUM
     SALES
     COMPUTE DIFF/D12.2 = LAST SALE - SALE;
BY COUNTRY
END

Best Regards,

Jimmy Pang

Hi,

I am not sure if that would work for

TABLE FILE NEO_SHIPMENT_DATA
SUM
COMPUTE B1/F8C = MONTH1 / 1000; AS '&M1'
COMPUTE B2/F8C = MONTH2 / 1000; AS '&M2'
COMPUTE B3/F8C = MONTH3 / 1000; AS '&M3'
COMPUTE B4/F8C = MONTH4 / 1000; AS '&M4'
COMPUTE B5/F8C = MONTH5 / 1000; AS '&M5'
COMPUTE B6/F8C = MONTH6 / 1000; AS '&M6'
COMPUTE B7/F8C = MONTH7 / 1000; AS '&M7'
COMPUTE B8/F8C = MONTH8 / 1000; AS '&M8'
COMPUTE B9/F8C = MONTH9 / 1000; AS '&M9'
COMPUTE B10/F8C = MONTH10 / 1000; AS '&M10'
COMPUTE B11/F8C = MONTH11 / 1000; AS '&M11'
COMPUTE B12/F8C = MONTH12 / 1000; AS '&M12'
COMPUTE ROWTOTAL/F8C = B1 + B2 + B3 + B4 + B5 + B6 + B7 + B8 + B9 + B10 + B11 + B12;


AS 'Total'
BY K AS '$K'
BY HIGHEST CALENDAR_FISCAL_YEAR AS 'Year'
ON TABLE PCHOLD FORMAT EXL2K
END

where M1, M2 so on are the 12 fiscal months starting April, May..

Please advise.

Thank you.
KK

KK, You may need to do this in 2 pass, i.e hold summarized data in a file and then do a table file on that hold file like the example below.
The answer lies in the "LAST" command but I notice since you need the column difference in a third row you would need to use the subfoot to get the difference as a row.


TABLE FILE CAR
SUM RETAIL_COST
DEALER_COST
COMPUTE DIFF1/P10.2C = RETAIL_COST - LAST RETAIL_COST ; NOPRINT
COMPUTE DIFF2/P10.2C = DEALER_COST - LAST DEALER_COST ; NOPRINT
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE'
ON TABLE SUBFOOT
"DIFFERENCE:<+0>-*ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=9,FONT='ARIAL',HEADALIGN=BODY,$
TYPE=TABFOOTING,ITEM=1,JUSTIFY=LEFT,$
TYPE=TABFOOTING,ITEM=2,JUSTIFY=RIGHT,$
TYPE=TABFOOTING,ITEM=3,JUSTIFY=RIGHT,$
ENDSTYLE
END
-RUN

-Yogesh Patel
------------------------------------------------------------------------
PROD: WF 764 on Linux Apache tomcat v5.5
DEV: WF 768 on Linux

KK, depends on how you structure your data. If you only have 2 years of monthly shipments per record, you can try this:

TABLE FILE CAR
SUM
SALES AS 'This Year' OVER
DCOST AS 'Last Year' OVER 
COMPUTE PROFIT/D12 = SALES - DCOST; AS 'Diff'
BY COUNTRY
ACROSS CAR
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE ROW-TOTAL
END
  

Hua

Or you can take a look at Financial Modeling Language (FML)
it gives you a lot of other ways to create a report

Taking ideas from earlier posts (use of LAST and SUBFOOT), here is one possible solution. If your data source contains only two years of data, then your BY HIGHEST clause will work. But if your data contains more than two years data, then you will need to change it to

BY HIGHEST 2 YEAR AS 'Year'

if you want to limit the report to the latest two years only. I added a third year to the test data so you could see how this report would work for multiple years.

The first SUM verb retrieves the latest year, which is used to prevent a YOY subfoot row from being generated for the first year in the report (WHEN YEAR LT C1; ).

APP FILEDEF SHIPMAS DISK shipdata.mas
-RUN
-WRITE SHIPMAS FILENAME=SHIPDATA,SUFFIX=FIX
-WRITE SHIPMAS SEGNAME=SHIPDATA,SEGTYPE=S0
-WRITE SHIPMAS FIELDNAME=K       ,ALIAS=K       ,FORMAT=A10,ACTUAL=A10,$
-WRITE SHIPMAS FIELDNAME=YEAR    ,ALIAS=YEAR    ,FORMAT=YY ,ACTUAL=A4 ,$
-REPEAT :ENDREPEAT1 FOR &I FROM 1 TO 12
-WRITE SHIPMAS FIELDNAME=MONTH&I ,ALIAS=MONTH&I ,FORMAT=D8 ,ACTUAL=A7 ,$
-:ENDREPEAT1
-*
APP FI SHIPDATA DISK shipdata.ftm
-RUN
-WRITE SHIPDATA Shipment  2007 999000 888000 777000 666000 555000 444000 333000 222000 111000 987000 654000 321000
-WRITE SHIPDATA Shipment  2008 741000 764000 818000 758000 776000 833000 682000 794000 516000 506000 497000 597000
-WRITE SHIPDATA Shipment  2009 424000 559000 560000 537000 451000 606000 588000 392000      0      0      0      0
-RUN
-SET &M1 = 'Apr-09';
-SET &M2 = 'May-09';
-SET &M3 = 'Jun-09';
-SET &M4 = 'Jul-09';
-SET &M5 = 'Aug-09';
-SET &M6 = 'Sep-09';
-SET &M7 = 'Oct-09';
-SET &M8 = 'Nov-09';
-SET &M9 = 'Dec-09';
-SET &M10 = 'Jan-10';
-SET &M11 = 'Feb-10';
-SET &M12 = 'Mar-10';
-*
TABLE FILE SHIPDATA
  SUM MAX.YEAR NOPRINT
  SUM
-REPEAT :ENDREPEAT2 FOR &I FROM 1 TO 12
  COMPUTE B&I/F8C = MONTH&I / 1000 ; AS '&M.&I'
  COMPUTE DIFFMTH&I/F8C = LAST B&I - B&I ; NOPRINT
-:ENDREPEAT2
  COMPUTE ROWTOTAL/F8C = B1 + B2 + B3 + B4 + B5 + B6 + B7 + B8 + B9 + B10 + B11 + B12; AS 'Total'
  COMPUTE TOTDIFF/F8C  = LAST ROWTOTAL - ROWTOTAL ; NOPRINT
  BY K AS '$K'
  BY HIGHEST YEAR AS 'Year'
  ON YEAR SUBFOOT
     "YOY<+0> <DIFFMTH1<DIFFMTH2<DIFFMTH3<DIFFMTH4<DIFFMTH5<DIFFMTH6<DIFFMTH7<DIFFMTH8<DIFFMTH9<DIFFMTH10<DIFFMTH11<DIFFMTH12<TOTDIFF"
     " "
     WHEN YEAR LT C1 ;
   ON TABLE NOTOTAL
   ON TABLE SET HTMLCSS ON
   ON TABLE PCHOLD FORMAT EXL2K
   ON TABLE SET STYLESHEET *
      TYPE=SUBHEAD, HEADALIGN=BODY, $
      TYPE=SUBFOOT, HEADALIGN=BODY, $
      TYPE=SUBFOOT, OBJECT=FIELD, JUSTIFY=RIGHT, $
   ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,

Simplest and most efficient/effective method is, imho and as Frank suggests, to use FML amd use RECAP to get your YOY values.

T

This message has been edited. Last edited by: Tony A,

KK,

Two questions:
1. For each K, how many CALENDAR_FISCAL_YEAR do you have? Only 2?
2. Do you have 1 K or many?

Thank you all for your valuable suggestions.

 Two questions:
1. For each K, how many CALENDAR_FISCAL_YEAR do you have? Only 2?
2. Do you have 1 K or many?
 

1. 2 years. only 2.
2. 1 K column.

Dan Satchell's suggestion looks appropriate to the way the data is structured. But I would also like to consider FML as another approach.

Thank you,
KK

Dan Satchell's solution works great. Thank you Dan !!.

KK - you are welcome.