I have a column on the report the calculates the Average days past due for each Business_Unit. I would like to get an Average of the Averages at the end of the report. Everything I have tried gives me a Subtotal for each Business_Unit rather the a column total showing The overall Average. Can anyone help?



TABLE FILE DW_FACT_ACCTS_REC
SUM
CUSTOMER
AVE.DAYS_FROM_DUE_DATE AS 'AVERAGE,DAYS FROM,DUE DATE'
DAYS_FROM_DUE_DATE AS 'SUM OF,DAYS FROM,DUE DATE'
CNT.DAYS_FROM_DUE_DATE AS 'COUNT,OF PAST DUE,INVOICES,BY BU'
BY BUSINESS_UNIT_CODE


-* ON BUSINESS_UNIT_CODE SUMMARIZE AVE. DAYS_FROM_DUE_DATE Returns a SUBFOOT Total

HEADING

FOOTING
"As of <+0>&DATEtMDYY <+0> Page WHERE ( OVERDUE EQ 'Y' ) AND ( DAYS_FROM_DUE_DATE NE ' ' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

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

The WITHIN clause may do what you need:

TABLE FILE CAR
SUM
SALES
AVE.SALES 
AVE.SALES WITHIN COUNTRY
BY COUNTRY
BY CAR
ON TABLE SUMMARIZE
END

Thank-you for your help. I tried the WITHIN command with no luck. The WITHIN command averages the individual Business Units but not the column of averages placing a result at the bottom of the report.

Again thanks for your support.

What is your denominator supposed to be to calculate the average of averages?

You could try doing a RECAP of the AVE.DAYS or C2 in column notation and then dividing by whatever you deem the denominator to be.

The denominator would be the COUNT of Rows in the report, the numerator would be the SUM of the values in the column.

ON TABLE RECAP AVE_AVE_DAYS/format=C2/rowcount;

You'll have to calculate the rowcount, possibly NOPRINT it in the report and use its column number in the calculation.

Try that.

Thank-you

I learned something today from Noreen
AVE.field is weighted
AVE. field (with a blank after the period) is unweighted.
So...
on your columns, use AVE.field
and on your SUBTOTAL use AVE. field to get an average of the averages...