Averages 1

[JoaoPinto]

Is there a way to display averages when using SUMMARIZE?
I currently have this output

Report from 02/01/03 to 02/10/03

REGION #loc #oflonotfor %notforw

002 616 43 6.98
003 711 17 2.39
006 781 94 12.04
007 498 19 3.82
008 609 20 3.28
016 1035 43 4.15
018 537 38 7.08
020 533 181 33.96
021 928 74 7.97
027 148 1 .68
041 793 41 5.17
051 665 124 18.65
052 1182 92 7.78
090 676 33 4.88

TOTAL
9712 820 8.44

It's getting the average Percentage but I'd also like the Average of the other two columns instead of the total? Is that possible?

Thanks,

 

[GrSw]

I do not know if it is exactly what you need, but you can use SUBFOOT instead of SUMMARIZE. If your output is text you can position the total values in proper columns using spot markers <n. If your output is HTML you can use FOCUS stylesheet with the option HEADALIGN=BODY.
An example:

TABLE FILE CAR
SUM SALES AVE.SEATS
BY COUNTRY
ON TABLE SUBFOOT
&quot;TOTAL AVERAGE:<AVE.SALES<AVE.SEATS&quot;
ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY, STYLE=BOLD, $
ENDSTYLE
END

You can use RECAP along with COMPUTEs as well, in order to have more sophisticated totals, than just the average.

Grzegorz

 

[GrSw]

Correction:

It does not produce the correct results !!!

But I think that combination of COMPUTEs, RECAP and HEADALIGN stylesheet, could lead to the proper solution. Maybe there is even simpler way ?

Let me know if you found the solution, or not.

Grzegorz

 

[JoaoPinto]

Well I can get Totals written out in the SUBFOOT section and I'm using a round about way to count the number of records:

DEFINE FILE TFINAL
THISCNT/I9 = 1;
END

TABLE FILE TFINAL
PRINT
MYDATE
APY_ROLOC
LOA_ROLOC
ON TABLE SUBFOOT
&quot;Average: <TOT.APY_ROLOC <TOT.LOA_ROLOC <TOT.THISCNT&quot;
ON TABLE SET PAGE-NUM OFF
END

The above lists the three numbers I need to calculate the two averages, but how do I use COMPUTE in the SUBFOOT?

ON TABLE SUBFOOT
&quot;Average: <COMPUTE TOT.APY_ROLOC/TOT.THISCNT;
<COMPUTE TOT.LOA_ROLOC/TOT.THISCNT;&quot;

I tried the above but it gives errors?

Thanks,

 

[JoaoPinto]

I've managed to get it working

DEFINE FILE TFINAL
THISCNT/I9 = 1;
END

TABLE FILE TFINAL
PRINT
MYDATE AS 'Weekly Totals'
LOA_ROLOC AS 'Total Sites'
NOTFOR AS 'Total Not Forwarding'
COMPUTE AVEAPY/I9 = TOT.NOTFOR/TOT.THISCNT; NOPRINT
COMPUTE AVELOA/I9 = TOT.LOA_ROLOC/TOT.THISCNT; NOPRINT
ON TABLE SUBFOOT
&quot;Average: <AVEAPY <AVELOA <AVEPER&quot;

Thanks,

 

[GrSw]

One of the solution is to COMPUTE the average value and then use it within SUBFOOT, for example:

TABLE FILE CAR
SUM SALES
COMPUTE CNTS/D12.2 = IF COUNTRY NE LAST COUNTRY THEN CNT + 1
ELSE CNTS; NOPRINT
COMPUTE TOTSAL/D12.2=IF COUNTRY NE LAST COUNTRY THEN TOTSAL+SALES
ELSE TOTSAL; NOPRINT
COMPUTE AVGSAL/D12.2=TOTSAL/CNTS; NOPRINT
BY COUNTRY
ON TABLE SUBFOOT
-* The COMPUTEd value is used in the SUBFOOT below:
&quot;TOTAL AVERAGE:<AVGSAL&quot;
ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY, STYLE=BOLD, $
ENDSTYLE
END

Probably there is a simpler solution, but this one works at least.

Hope this helps
Grzegorz

 

[GrSw]

Yes, using TOT. make the things simpler (two computes instead of three), and it also works with SUM:

TABLE FILE CAR
ON TABLE SET PAGE-NUM OFF
SUM SALES
COMPUTE CNTS/D12.2 = IF COUNTRY NE LAST COUNTRY THEN CNT + 1
ELSE CNTS; NOPRINT
COMPUTE AVGSAL/D12.2=TOT.SALES/(TOT.CNTS-1); NOPRINT
BY COUNTRY
ON TABLE SUBFOOT
-* The COMPUTEd value is used in the SUBFOOT below:
&quot;TOTAL AVERAGE:<AVGSAL&quot;
ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY, STYLE=BOLD, $
ENDSTYLE
END

but I suppose there is even more simple solution
(I cannot find it now)

Grzegorz

 

[focwizard]

Well, if you're willing to be creative, you can do it automatically. Here's sample code:

DEFINE FILE CAR
ONE/I5=1;
END
TABLE FILE CAR
SUM SALES
FST.ONE
COMPUTE TSALES/D7=SALES/FST.ONE;
BY COUNTRY
ON TABLE SUMMARIZE
END

When you get the output, you'll notice that the column TSALES, looks like SALES, except on the SUMMARIZE line, where it's the average of the SALES figures. What this does is create a column of 1's (the DEFINEd field ONE, with the FST. prefix), and a COMPUTE divides the original value by 1, giving the original value. On the SUMMARIZE, the COMPUTE is redone, using SUBTOTAL values. The SUBTOTAL of the 1's is the count of the number of rows. The resultant division is the average. So, if you NOPRINT SALES and FST.ONE, you get a single column, with what you want.