Consider the following example:

  
DEFINE FILE GGSALES
QTR/QYY = DATE;
END

TABLE FILE GGSALES
SUM 
-*UNITS/D8
DOLLARS/D12M
BY PRODUCT 
ACROSS QTR
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
END

It shows sales by product across 8 quarters. What is the easiest way to add one more column to caculate the average variance between quarters. So (q4 1997 sales - q1 1996) sales divided by 8 . For example Biscoitt would have a trend value of -9715.

Thank you

This message has been edited. Last edited by: Joey Sandoval,

One approach:

SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON
-*
DEFINE FILE GGSALES
 QTR/QYY = DATE;
END
-*
TABLE FILE GGSALES
 SUM
  MIN.QTR NOPRINT
  MAX.QTR NOPRINT
-*
 SUM
  DOLLARS/D12M
  CNT.DST.QTR AS 'QTR_CNT'
  COMPUTE FIRST_QTR/D12 = IF (QTR EQ MIN.QTR) THEN DOLLARS ELSE 0 ;
  COMPUTE FINAL_QTR/D12 = IF (QTR EQ MAX.QTR) THEN DOLLARS ELSE 0 ;
 BY PRODUCT 
 BY QTR
 ON TABLE HOLD AS HOLDX
END
-*
TABLE FILE HOLDX
 SUM
  QTR_CNT   NOPRINT
  FIRST_QTR NOPRINT
  FINAL_QTR NOPRINT
 BY PRODUCT
-*
 SUM DOLLARS
 BY PRODUCT
 ACROSS QTR
 COMPUTE VARIANCE/D12M = (FINAL_QTR - FIRST_QTR) / QTR_CNT ;
 ON TABLE PCHOLD FORMAT EXL2K
 ON TABLE SET HTMLCSS ON
 ON TABLE SET STYLE *
  INCLUDE = endeflt, $
 ENDSTYLE
END

here is what i would do:

DEFINE FILE GGSALES
QTR/QYY = DATE;
END

TABLE FILE GGSALES
SUM 
-*UNITS/D8
DOLLARS/D12M
COMPUTE
VARIANCE/D10.2% = IF ((PRODUCT EQ LAST PRODUCT) AND (QTR GT LAST QTR)) THEN (((DOLLARS - LAST DOLLARS) / LAST DOLLARS) * 100) ELSE 0 ;
BY PRODUCT 
ACROSS QTR
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
END  

Thank you guys for your help! I was able to get 95% of what I need.. now I am just having trouble with the total line. Consider the revised example where I am calculating the Standard Deviation of dollars sold for all 8 quarters along with the overall variance from beginning to end.

 
SET ALL = ON
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
SET BYDISPLAY = ON
SET HOLDMISS = ON
SET MISSING = ON

DEFINE FILE GGSALES
QTR/QYY = DATE;
END

TABLE FILE GGSALES
SUM 
	MIN.QTR NOPRINT
SUM
DOLLARS/D12.2CM
COMPUTE VARIANCE/D12M MISSING ON = IF MIN.QTR = QTR THEN MISSING ELSE DOLLARS - LAST DOLLARS;
BY PRODUCT 
BY QTR
ON TABLE HOLD AS HOLD1
END

TABLE FILE HOLD1
SUM
VARIANCE NOPRINT
CNT.DOLLARS NOPRINT
ASQ.DOLLARS/D12.2CM NOPRINT
AVE.DOLLARS NOPRINT
COMPUTE PVAR/D12.2 = (ASQ.DOLLARS - (AVE.DOLLARS * AVE.DOLLARS))  * (CNT.DOLLARS / (CNT.DOLLARS-1)); NOPRINT
BY PRODUCT AS 'Product'
SUM DOLLARS/D12M AS 'Sales'
BY PRODUCT AS 'Product'
ACROSS QTR AS 'Period'
COMPUTE STD_DEV/D12M = SQRT(PVAR); AS 'Standard Deviation'
COMPUTE VAR/D12M = VARIANCE; AS 'Trend'
ON TABLE SUMMARIZE 
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
END
 

How can I make the Standard Deviation calculation accurate on the total line for all quarters? The total Standard Variation value should equal 134,222. On Table summarize or recompute does not seem to work in this case...

sigh.
first , a standard deviation is valid for normal distributions.
you haven't checked for the shape of the distribution, and don't have enough values to do a test. takes 30.
second, you've used a wildly convoluted way of calculating an sd; i'ld recommend stepping back , and pacing thru the calculations in a more auditable way.
and last, make the TOTAL its own variable. add it to your data set.
if you take off the NOPRINTs, you'll see that your value of N for your total line would be 80, not 8, so just don't do it this way. at all.
DEFINE FILE ibisamp/GGSALES
QTR/QYY = DATE;
END
-RUN
TABLE FILE ibisamp/GGSALES
SUM DOLLARS BY PRODUCT BY QTR ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
SUM
AVE.DOLLARS
BY PRODUCT
SUM DOLLARS AND COMPUTE DIF/D22 =(C2-C1)**2;
BY PRODUCT
BY QTR
ON TABLE HOLD AS HOLD1
END
-* n-1 for sample, n for population
TABLE FILE HOLD1
SUM DIF NOPRINT
CNT.QTR AS N
AVE.DOLLARS AS MEAN

AND COMPUTE STDEV/D12=(C1/(C2-1))**.5;
BY PRODUCT
END

Woow..Susannah

I loved the Sigh...

Math was always my favourite at school...

Good you saw this mistake

Thank you Susannah. I was able to use your advice to write a solution by calculating the total in it's own dataset and joining it to the quarterly dataset using a more statement. Although, you thought my Std Deviation calculation was convoluted, I disagree. I thought the multiverb solution was more elegant and effecient because it used less hold files and built-in ASQ prefix.

 
SET ALL = ON
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
SET BYDISPLAY = ON
SET HOLDMISS = ON
SET MISSING = ON

DEFINE FILE GGSALES
QTR/QYY = DATE;
END

TABLE FILE GGSALES
SUM
	MIN.QTR NOPRINT
SUM
DOLLARS/D12.2CM
COMPUTE VARIANCE/D12M MISSING ON = IF MIN.QTR = QTR THEN MISSING ELSE DOLLARS - LAST DOLLARS;
BY PRODUCT
BY QTR
ON TABLE HOLD AS QTRHOLD
END

TABLE FILE QTRHOLD
SUM
VARIANCE NOPRINT
CNT.DOLLARS  NOPRINT
ASQ.DOLLARS/D12.2CM  NOPRINT
AVE.DOLLARS  NOPRINT
COMPUTE PVAR/D12.2 = (ASQ.DOLLARS - (AVE.DOLLARS * AVE.DOLLARS))  * (CNT.DOLLARS / (CNT.DOLLARS-1)); NOPRINT
COMPUTE STD_DEV/D12M = SQRT(PVAR); AS 'STD_DEV'
COMPUTE VAR/D12M = VARIANCE; 
BY PRODUCT 
SUM DOLLARS
BY PRODUCT 
BY QTR AS 'QTR'
ON TABLE HOLD AS QTRHOLD2 FORMAT FOCUS
END


TABLE FILE GGSALES
SUM MIN.QTR NOPRINT
SUM
DOLLARS/D12.2CM
COMPUTE VARIANCE/D12M MISSING ON = IF MIN.QTR = QTR THEN MISSING ELSE DOLLARS - LAST DOLLARS;
BY QTR
ON TABLE HOLD AS TOTALHOLD
END

DEFINE FILE TOTALHOLD
PRODUCT/A16 = 'Total';
END

TABLE FILE TOTALHOLD
SUM
VARIANCE NOPRINT
CNT.DOLLARS NOPRINT
ASQ.DOLLARS NOPRINT
AVE.DOLLARS NOPRINT
COMPUTE PVAR/D12.2 = (ASQ.DOLLARS - (AVE.DOLLARS * AVE.DOLLARS))  * (CNT.DOLLARS / (CNT.DOLLARS-1)); NOPRINT
COMPUTE STD_DEV/D12M = SQRT(PVAR); AS 'STD_DEV' 
COMPUTE VAR/D12M = VARIANCE; 
SUM DOLLARS
BY PRODUCT
BY QTR AS 'QTR'
ON TABLE HOLD AS TOTALHOLD2 FORMAT FOCUS
END


TABLE FILE QTRHOLD2
PRINT 
*
ON TABLE HOLD AS FINALHOLD FORMAT FOCUS
MORE 
FILE TOTALHOLD2
END


TABLE FILE FINALHOLD
SUM 
COMPUTE STD_DEV2/D12M = FST.STD_DEV; NOPRINT
COMPUTE VAR2/D12M = FST.VAR; NOPRINT
BY PRODUCT

SUM DOLLARS
BY PRODUCT
ACROSS QTR

COMPUTE STD_DEV3/D12M = STD_DEV2; AS 'Standard Deviation'
COMPUTE VAR3/D12M = VAR2; AS 'Trend'

ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
END

 

disagree all you want, but heed Francis' advice
old coders never leave a mess.

I understand the code looks messy, but part of this has to do with the report formatting requirements that I should of mentioned earlier. Our personnel want to see all periods in an across layout, with the Std Deviation and Total Variance calculations as the final 2 columns.

I do tend to try and minimize the amount of hold files used when doing calculations because I've always thought less code is better, but I do understand your point of view of making a more step-by-step design. I will keep this in mind. Thank you.