Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Need column total true avearages

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Need column total true avearages
 Login/Join
 
Silver Member
posted
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,


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Report This Post
Virtuoso
posted Hide Post
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
 


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Silver Member
posted Hide Post
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.


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
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?


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
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!


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Report This Post
Virtuoso
posted Hide Post
My pleasure.

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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Need column total true avearages

Copyright © 1996-2020 Information Builders