I have calculated a percentage and I want to sum it up and display the sum inside a footer as a sanity check.

I have tried using sum, and I have tried creating a new define, setting it equal to 0, and then adding the percents to it, but neither of these seem to work

Here is an rough idea of how my code looks:
(note this will not run it is just an example)

Define file car
percent/D12.2 = valA/valB
totpercent/D3.2 = ??
END
table file car
on maker subfoot
"Totpercent= ??"

Why not simply use a SUBTOTAL instead of a SUBFOOT?

DEFINE FILE CAR
PERCENT/D12.2 = DEALER_COST/RETAIL_COST;
END
TABLE FILE CAR
SUM
SALES
PERCENT
DEALER_COST
RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
ON COUNTRY SUBTOTAL
END

quote:

Originally posted by Tom Flynn:
Normally, % is calculated on a row-by-row basis with a COMPUTE, but:

ON MAKER SUBFOOT
"Total Percent =
Edit: Disable HTML

This displays the sum of ALL the percents once for every record, I only want it to sum up the percents from that specific record
(For example if there are 70 records, it will display 7,000 70 times)

quote:

Originally posted by Francis Mariani:
Why not simply use a SUBTOTAL instead of a SUBFOOT?

Well I have some other things id like to display on that same line but at least I can get a working sum of the percents this way. Maybe it will be easier to subtotal and display the other stuff on the next line..

Edit: Also subtotal as '' still prints the BY field name you are subtotaling on...I just want it to to display Totals: so subfoot gives me more control in that regard.

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

For more control and to add other details to your subfoot you can use RECAP.

-*SAMPLE USING RECAP and SUBFOOT

DEFINE FILE CAR
DFN_PCT/D12.2= RETAIL_COST / DEALER_COST;
END
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
DFN_PCT
BY COUNTRY
BY CAR
ON COUNTRY RECAP
TOTPCT/D12.2 = RETAIL_COST / DEALER_COST;
ON COUNTRY SUBFOOT
""
"Recalc of percentage TOTPCT " (use the less than sign in front of fieldname)
""
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
RIGHTGAP=0.125000,
$
END

I am having a similar type of problem. I have a report that I want to have subtotals on three of the columns, but other columns are actually calculated percentages based on the subtotals.

For example:

PCT1 is calculated as COL1/GRANDTOTALCOL1
etc.

     COL1    COL2  PCT1   PCT2   
       15      10  37.5%  33.3%
        5      10  12.5%  33.3%
STOT:  20      20  50.0%  66.7%

       15       5  37.5%  16.7%
        2       1   5.0%   3.3%
        3       4   7.5%  13.3%
STOT:  20      10  50.0%  33.3%

GTOT:  40      30 100.0% 100.0%

This is the result of a stored procedure. What I'm doing right now is:

SQL SQLMSS
EX DB.dbo.STOREDPROC ;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS REPDATA
END

DEFINE FILE REPDATA
-* HERE I am trying to calculate total and 
-* percents
SUMCOL1 = SUM(COL1);
SUMCOL2 = SUM(COL2);
-* the next two lines are not working... 
PCTCOL1 = COL1/SUMCOL1;
PCTCOL2 = COL2/SUMCOL2;
END

TABLE FILE REPDATA
PRINT
    LabelData
    COL1/D12M
    COL2/D12M
    PCTCOL1/D4.1%
    PCTCOL2/D4.1%

BY GroupData NOPRINT
ON GroupData SUBTOTAL

I have the subtotal line and grand total lines working great but they are also summing up the percentages which is obviously not what I want. Until I get the percentages calculations working, I am just hardcoding in a value for percent to display.

So I have two questions:

1) How to calculate the percentage of a column against the grand total

2) How to print a subtotal showing THAT percentage and not the subtotal of that column value -- I've tried doing a SUBFOOT but then I run into issues with alignment that I can't work around.

As a starting point, the following is a simplistic example:

TABLE FILE REPDATA
SUM COL1 COL2
PRINT
    COL1/D12M
    COL2/D12M
COMPUTE PCTCOL1/D4.1%=(C3/C1)/100;
COMPUTE PCTCOL2/D4.1%=(C4/C2)/100;
BY GroupData NOPRINT
ON GroupData RECOMPUTE

using multi verb request to SUM each column and column notation to refer to each column, where C1 refers to the SUM COL1 (i.e. the TOTAL), C2 to the SUM COL2, C3 to the PRINT COL1 and C4 to the PRINT COL2.
(Not sure this is correct 'cos having issue with it, see this topic. )

This message has been edited. Last edited by: Alan B,

This works to print out the correct percent calculation, but it doesn't allow me to change the heading of the computed percent column. I was doing a "PRINT PCTCOL1 as 'heading'" -- is there another way to set the heading?

The only thing that's not showing correctly is the grand total line -- which should be showing all 100% across the columns, but instead is showing some rather nonsensical numbers.

You can do AS 'heading' on a COMPUTE as well. It comes AFTER the semicolon-

You probably also want to multiply by 100 instead of dividing by 100:
COMPUTE PCTCOL1/D4.1%=(C3/C1)*100;
COMPUTE PCTCOL2/D4.1%=(C4/C2)*100;

Sorry, Darin, yes multiply.

It's the effect of all you guys having enjoyed the summit so much, driving me loopy over here!

Okay, now the official version.

DEFINE FILE Fn
CTR/I5 WITH fieldname= 1;
END
TABLE FILE Fn
SUM TOT.COL1 NOPRINT
    TOT.COL2 NOPRINT
    CTR NOPRINT
    COL1/D12M
    COL2/D12M
COMPUTE 
    PCTCOL1/D4.1% = (C4/(C1/C3))*100; 
    PCTCOL2/D4.1% = (C6/(C2/C3))*100; 
BY groupData NOPRINT
ON groupData RECOMPUTE

Based on response from IB here.