Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Calculating Percent of Row total based on Column total
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Calculating Percent of Row total based on Column total
 Login/Join
 
Gold member
posted
I have a report that I am trying to add a percent column to. The report has plant location as a by and month as the across. The data is a count of records. I have a total column that counts the total of each row. Now I want to add a column to calculate the percent of each row based on the total of the Row Totals Column. In other words:

Plant location......M1....M2.....Total....Pct
a........................1........2.........3........30%
b........................1........3.........4........40%
c........................2........0.........2........20%
d........................0........1.........1........10%
Total......................................10

I want the row total divided by the column total.

Here is the code of my report currently:
TABLE FILE STAR_SALES_DETAIL
SUM
CNT.SALES_LINE_COUNT/I9C AS 'COUNT,SALES_LINE_COUNT'
BY LOWEST STAR_SALES_DETAIL.D_SHIP_PLANT_DC.PLANT_DC_CODE AS 'DC/Plant'
ACROSS STAR_SALES_DETAIL.D_GL_PERIOD_DATE.MONTH_NUMBER
WHERE ( STAR_SALES_DETAIL.D_ORDER_TYPE.ORDER_TYPE_CODE EQ 'SF');
WHERE (STAR_SALES_DETAIL.D_GL_PERIOD_DATE.YEAR_4 EQ 2014);
WHERE STAR_SALES_DETAIL.F_SALES_DETAIL.RTN_ALLOW_AMOUNT EQ '0';
WHERE ( STAR_SALES_DETAIL.F_SALES_HEADER.INVOICE_NUMBER NE MISSING );
WHERE (STAR_SALES_DETAIL.D_ITEM.ITEM_DESCRIPTION NE 'A/R Rounding Adjustment');
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE ROW-TOTAL AS 'YTD Lines'
ON TABLE COLUMN-TOTAL AS '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

Any help would be appreciated. Thanks.

This message has been edited. Last edited by: <Kathryn Henning>,
 
Posts: 51 | Registered: August 15, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
This example may help:
TABLE FILE CAR
SUM CNT.SALES NOPRINT
SUM CNT.SALES NOPRINT
BY COUNTRY
SUM CNT.SALES
ACROSS SEATS 
COMPUTE TOT_COUNT/I11 = C2;
COMPUTE PCT/F7.2 = (C2/C1)*100;
BY COUNTRY
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
For the 2 compute lines, if I want to divide my total columns, do I need to substitute something for C2 and C1? I understand that these refer to the columns, but is there a different designation for the total columns?

I imagine that that it would be something like this:
COMPUTE PCT/F7.2 = (ROW TOTAL / COLUMN TOTAL)*100

How do I designate the ROW TOTAL and COLUMN TOTAL for the purposes of this compute?


WebFOCUS 8007
 
Posts: 51 | Registered: August 15, 2014Reply With QuoteReport This Post
Master
posted Hide Post
Are you looking for something like this. Modified version of Alan's code.

APP PATH IBISAMP
-RUN
DEFINE FILE CAR
SALES_2/D12.2=SALES
END
TABLE FILE CAR
SUM
COMPUTE COLUMN_TOT/D12=CNT.SALES; NOPRINT
SUM
COMPUTE ROW_TOT/D12=CNT.SALES_2; NOPRINT
BY COUNTRY
SUM CNT.SALES
ACROSS SEATS
COMPUTE ROW_COUNT/I11 = ROW_TOT;
COMPUTE PCT/F7.2 = (ROW_COUNT/COLUMN_TOT)*100;
BY COUNTRY
END


Thanks,
Ram
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
I used the first example code from Alan and figured out how to translate it into my code. It works perfectly! Thanks.


WebFOCUS 8007
 
Posts: 51 | Registered: August 15, 2014Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Calculating Percent of Row total based on Column total

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.