[SOLVED] Calculating Percent of Row total based on Column total
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:
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>,
August 18, 2014, 10:08 AM
Alan B
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
August 18, 2014, 10:19 AM
Scott Matson
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
August 18, 2014, 10:33 AM
Ram Prasad E
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