I am trying to create a new table and am having difficulties. The data in the CAR file doesn’t allow me to recreate my situation like I want, so I’m using the MOVIES file that hopefully everyone has.

I want my BY field to be Category and my across to be Rating. Then I want to average the list price for each of those combos. Pretty simple. But my problem then becomes that I want to give a row average and a column average for each of these things. I have been able to figure out how to get a row average but I cannot figure out how to get column totals. I tried to use ON TABLE SUMMARIZE and ON TABLE RECOMPUTE but those are giving me averages of the averages. And I don’t want that. I want average of the raw data.

Here is how I’m building this report out right now that is giving me everything I need except those column totals.

TABLE FILE MOVIES
SUM
AVE.MOVIES.MOVINFO.LISTPR
LISTPR WITHIN CATEGORY NOPRINT
CNT.LISTPR WITHIN CATEGORY NOPRINT
BY LOWEST MOVIES.MOVINFO.CATEGORY
ACROSS LOWEST MOVIES.MOVINFO.RATING
COMPUTE ROW_TOTAL/D12.2 = LISTPR / CNT.LISTPR; AS 'Cat Total'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

It seems like this is something that should be able to be done fairly easily but I can’t figure it out. Any ideas?

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

Is this what you're looking for?

 
TABLE FILE MOVIES
SUM
AVE.MOVIES.MOVINFO.LISTPR
LISTPR WITHIN CATEGORY NOPRINT
CNT.LISTPR WITHIN CATEGORY NOPRINT
BY LOWEST MOVIES.MOVINFO.CATEGORY
ACROSS LOWEST MOVIES.MOVINFO.RATING
COMPUTE ROW_TOTAL/D12.2 = LISTPR / CNT.LISTPR; AS 'Cat Total'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SUMMARIZE
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
 

I added AVE. on the summarize line so it looks like this:
ON TABLE SUMMARIZE AVE.

The problem is that is giving us an average of the averages we see in the column. For example, for the rating G column, when I add that summarize line that gives me the total average of 43.46 for that column but if I go through and get all of the raw data (add it all up and then divide) I get the number of 40.295. Which is correct. I don't want to show the average of the averages. Hopefully that makes sense.

Is this what you're looking for ?

TABLE FILE MOVIES
SUM COMPUTE AVERAGE /D6.2C = SUM.LISTPR / CNT.LISTPR;
BY CATEGORY
ACROSS RATING 
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SUMMARIZE RECOMPUTE
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

Thanks Martin, that's getting closer. Your solution gives me my column averages like I need. Those look great but that lost my row averages. And I'm not finding a quick and easy way to add those in. I'm looking at that now.

But I do have a question regarding you using SUMMARIZE & RECOMPUTE together. I've not seen that before. Do you know what that does my using them together?

quote:

But I do have a question regarding you using SUMMARIZE & RECOMPUTE together. I've not seen that before. Do you know what that does my using them together?

No need to have RECOMPUTE since SUMMARIZE does it, my mistake.

-* With the Row Total
TABLE FILE MOVIES
SUM COMPUTE AVERAGE /D6.2C = SUM.LISTPR / CNT.LISTPR;
BY CATEGORY
ACROSS RATING ACROSS-TOTAL RECOMPUTE AS 'Row Total'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SUMMARIZE AS 'Column Total'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

Thank you Martin!

Your report looks to be giving me everything that I need. I was able to take your techniques and apply it to my real life report and my numbers look much better. Thanks so much for the help!

My pleasure.

Edit your first post and add [SOLVED] at the start of the subject.