exl2k - Prevent TOTAL of '% of TOTAL' from displaying in RECOMPUTE [SOLVED]

July 17, 2013, 06:11 PM

I have created a prototype in exl2k to show numeric counts (columns) with the total and a '% of TOTAL'
(pct.cnt) of that total.

I am trying to prevent the TOTAL of the '% of TOTAL' from displaying in the ON TABLE RECOMPUTE, along with
the other totals.

Can anyone help me? I have been playing with ON TABLE SUBFOOT, ON TABLE RECOMPUTE, etc., but it gets
a little monochromatic.

Here is the code:

-* File PLAY_CARS.fex
SET ASNAMES = ON
 
TABLE FILE CAR
PRINT
CAR
MODEL
SALES
BY COUNTRY
ON TABLE HOLD AS CX1
END
-RUN

DEFINE FILE CX1
ECAR/I9 = IF COUNTRY EQ 'ENGLAND' AND SALES GT 0 THEN 1 ELSE 0;
FCAR/I9 = IF COUNTRY EQ 'FRANCE'  AND SALES GT 0 THEN 1 ELSE 0;
ICAR/I9 = IF COUNTRY EQ 'ITALY'   AND SALES GT 0 THEN 1 ELSE 0;
JCAR/I9 = IF COUNTRY EQ 'JAPAN'   AND SALES GT 0 THEN 1 ELSE 0;
GCAR/I9 = IF COUNTRY EQ 'W GERMANY' AND SALES GT 0 THEN 1 ELSE 0;
END

TABLE FILE CX1
"POST DEFINE CODE"
PRINT
ECAR
FCAR
ICAR
JCAR
GCAR
COMPUTE ALLCARS/D12 = ECAR + FCAR + ICAR + JCAR  + GCAR;
BY COUNTRY
ON TABLE HOLD AS CX2
END
-RUN

TABLE FILE CX2
HEADING
" SUM THEM "
SUM
ALLCARS AS 'ATOT'
PCT.CNT.ALLCARS AS 'PCTOT'
ECAR
FCAR
ICAR
JCAR
GCAR
BY COUNTRY
ON TABLE HOLD AS CX2
END
-RUN

TABLE FILE CX2
HEADING
" FINAL REPORT "
" "
PRINT
ECAR AS 'England'
FCAR AS 'France'
ICAR AS 'Italy'
JCAR AS 'Japan'
GCAR AS 'W Germany'
ATOT AS 'Total'
PCTOT AS '% of,TOTAL'
BY COUNTRY
ON TABLE RECOMPUTE

ON TABLE PCHOLD FORMAT EXL2K
 
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
TYPE=REPORT,
FONT=ARIAL,
SIZE=9,
STYLE=NORMAL,
$
TYPE=HEADING,
HEADALIGN=BODY,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
STYLE=BOLD,
COLSPAN=8,
$
TYPE=HEADING,
LINE=2,
COLSPAN=8,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
STYLE=BOLD,
COLSPAN=8,
$
ENDSTYLE
END

-RUN

Thank you !

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

July 17, 2013, 06:42 PM

j.gross

You have PCTOT as a column of CX2, so the report against CX2 has no basis on which it can be "recomputed".

To recompute the percent, you need to include its numerator and denominator as (NOPRINT) columns, and COMPUTE the percent as a printed column. RECOMPUTE will then recomputed the percent column's summary value, based on the column-totals of the (hidden) numerator and denominator columns.

This message has been edited. Last edited by: j.gross,

July 18, 2013, 11:36 AM

Tomsweb

I solved the problem with this block of code at the bottom of the request.

 
TYPE=GRANDTOTAL,
COLUMN=PCTOT,
COLOR='WHITE',
$

Thanks

July 18, 2013, 01:49 PM

Tomsweb

quote:

Originally posted by Tomsweb:
I solved the problem with this block of code at the bottom of the request.
I also added the code JG recommended to make it correct.

 
SET ASNAMES = ON
-SET &ECHO = ALL;

TABLE FILE CAR
PRINT
CAR
MODEL
SALES
BY COUNTRY
ON TABLE HOLD AS CX1
END
-RUN

DEFINE FILE CX1
ECAR/I9 = IF COUNTRY EQ 'ENGLAND'   AND SALES GT 0 THEN 1 ELSE 0;
FCAR/I9 = IF COUNTRY EQ 'FRANCE'    AND SALES GT 0 THEN 1 ELSE 0;
ICAR/I9 = IF COUNTRY EQ 'ITALY'     AND SALES GT 0 THEN 1 ELSE 0;
JCAR/I9 = IF COUNTRY EQ 'JAPAN'     AND SALES GT 0 THEN 1 ELSE 0;
GCAR/I9 = IF COUNTRY EQ 'W GERMANY' AND SALES GT 0 THEN 1 ELSE 0;
END

TABLE FILE CX1
"POST DEFINE CODE"
PRINT
ECAR
FCAR
ICAR
JCAR
GCAR
COMPUTE ALLCARS/D12 = ECAR + FCAR + ICAR + JCAR  + GCAR;
BY COUNTRY
ON TABLE HOLD AS CX2
END
-RUN

TABLE FILE CX2
HEADING
" SUM THEM THE CORRECT WAY "
SUM
ALLCARS AS 'ATOT'
COMPUTE PCTOT/D7.2% = ALLCARS / TOT.ALLCARS * 100;
ECAR
FCAR
ICAR
JCAR
GCAR
BY COUNTRY
ON TABLE HOLD AS CX2
END
-RUN

?FF CX2
-RUN
-*EXIT

TABLE FILE CX2
HEADING
" FINAL REPORT "
" "
PRINT
ECAR AS 'England'
FCAR AS 'France'
ICAR AS 'Italy'
JCAR AS 'Japan'
GCAR AS 'W Germany'
ATOT AS 'Total'
PCTOT AS '% of,TOTAL'
BY COUNTRY
ON TABLE RECOMPUTE

ON TABLE PCHOLD FORMAT EXL2K

ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
TYPE=REPORT,
FONT=ARIAL,
SIZE=9,
STYLE=NORMAL,
$
TYPE=HEADING,
HEADALIGN=BODY,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
STYLE=BOLD,
COLSPAN=8,
$
TYPE=HEADING,
LINE=2,
COLSPAN=8,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
STYLE=BOLD,
COLSPAN=8,
$
TYPE=GRANDTOTAL,
COLUMN=PCTOT,
COLOR='WHITE',
$

Thanks JG!