Hi there,

I think it's due to the cold winter here that I feel completely lost with my little issue-

I have my table summing with BY and with ACROSS like that:

 TABLE FILE myTable
SUM    myMeasure
BY myMonth
ACROSS mySalesOrg
END

This gives me a table like that:

North South East
Jan 50 50 180
Feb 10 20 30
Mar 100 50 50
... ... ... ...
Dec 80 70 40


No I want on each row and column an average value for myMeasure, like:

North South East Ave
Jan 50 50 80 60
Feb 10 20 30 20
Mar 70 80 60 70
... ... ... ...
Dec 80 70 40 ...

Ave: 52 55 ... ...


Any help would be appreaciated.

Kind Regards,

Stefan

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

I used the CAR file to work on this solution. There is no elegance to this, but it might work for you.

TABLE FILE CAR
  SUM SEATS            NOPRINT
      CNT.DST.BODYTYPE NOPRINT
  AND COMPUTE BODYTYPE/A12 = 'Average';
  AND COMPUTE AVG_SEATS/I5 = SEATS / CNT.DST.BODYTYPE ; AS 'SEATS'
   BY COUNTRY
   ON TABLE HOLD AS HOLD1
END
-*
TABLE FILE CAR
  SUM SEATS           NOPRINT
      CNT.DST.COUNTRY NOPRINT
  AND COMPUTE COUNTRY/A10 = 'Average';
  AND COMPUTE AVG_SEATS/I5 = SEATS / CNT.DST.COUNTRY ; AS 'SEATS'
   BY BODYTYPE
   ON TABLE HOLD AS HOLD2
END
-*
TABLE FILE CAR
  SUM SEATS
   BY COUNTRY
   BY BODYTYPE
   ON TABLE HOLD AS HOLD3
END
-*
TABLE FILE HOLD1
PRINT COUNTRY
      BODYTYPE
      SEATS
   ON TABLE HOLD AS HOLD4
 MORE
 FILE HOLD2
 MORE
 FILE HOLD3
END
-*
DEFINE FILE HOLD4
 SORT_BODYTYPE/I1 = IF BODYTYPE EQ 'Average' THEN 1 ELSE 0 ;
 SORT_COUNTRY/I1  = IF COUNTRY  EQ 'Average' THEN 1 ELSE 0 ;
END
-*
TABLE FILE HOLD4
   SUM SEATS
    BY SORT_BODYTYPE NOPRINT
    BY BODYTYPE      AS ''
ACROSS SORT_COUNTRY  NOPRINT
ACROSS COUNTRY       AS ''
END

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

Dan,
thanks four your proposal. I used it and a little bit of [URL=http://techsupport.information...om/sps/22302042.html ]this technique[/URL] to get it workin:

DEFINE FILE myFile
myMeasure2/D6=myMeasure;
END

TABLE FILE myFile
SUM
	AVE.myMeasure2 NOPRINT
BY myMonth

SUM
  myMeasure 
BY myMonth 
ACROSS mySalesOrg
COMPUTE 'Month'/D6=AVE.myMeasure2 ;
ON TABLE SUMMARIZE AVE. myMeasure

So I get nearly the output I wanted to have:


North South East Ave
Jan 50 50 80 60
Feb 10 20 30 20
Mar 70 80 60 70
... ... ... ... ....
Dec 80 70 40 63

Ave: 52 55 ... ... XXX

But:

There is still one problem left: The value in the lower right corner (marked with 'XXX') isn't the average of the corresponding values, but the sum of the above column.

So I still don't have the clue.

quote:

ON TABLE SUMMARIZE AVE. myMeasure

I believe the problem is with the statement above. Change it to "ON TABLE SUMMARIZE AVE." (without the myMeasure) and see if that corrects the problem. Then your 'XXX' should become the average of the numbers in that column - the same as the calculation for all of the other columns.

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

Stefan,

Good work finding your solution on the IBI tech site. It is a much simpler solution than the one I proposed earlier. I initially tried something similar but could not figure out how to generate the averages row on the bottom of the matrix. I completely forgot about the SUMMARIZE AVE. usage. Thanks for reminding me!

Dan,

  ON TABLE SUMMARIZE AVE. 

works perfect.

Thanks alot.

Kind regards,

Stefan