Hello,

Here is a simple example
TABLE FILE CAR
SUM
SALES AS 'Somme'
PCT.SALES/D9.2% WITHIN TABLE WITHIN CAR AS '%'
BY COUNTRY
ACROSS CAR AS '' RECOMPUTE AS 'Totaux'
WHERE CAR LE 'F';

ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
END

If you run this example, you will see something like this
PAGE 1
ALFA ROMEO AUDI BMW DATSUN Totaux
COUNTRY Somme % Somme % Somme % Somme % Somme %
ITALY 30200 100,00% . . . . . . 30200 100,00%
JAPAN . . . . . . 43000 100,00% 43000 100,00%
W GERMANY . . 7800 100,00% 80390 100,00% . . 88190 200,00%
TOTAL 30200 100,00% 7800 100,00% 80390 100,00% 43000 100,00% 161390 400,00%

what I want for the last column is
First line ==> not 100% but 30200/161390 = 18,71%
Second line ==> not 100% but 43000/161390 = 26,64%
Third line ==> not 200% but 88190/161390 = 54,54 %
Last line ==> not 400% but 100%

How can I have this result ?
Thanks
Catherine

This message has been edited. Last edited by: Cati - France,

It's funny/sad that you ask this question right now! I had a problem with this in 2005 and I still have a problem, please see [ALWAYS OPEN] Calculating Percentages with Across and By. I don't think this will help you with the Totals...

Here's one approach - a little cumbersome, but it works.

SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY

DEFINE FILE CAR
 CARTEXT/A17    = ' ' | CAR ;
 TOTCARTEXT/A17 = 'TOTAUX';
END

TABLE FILE CAR
 SUM SALES/D9
 BY COUNTRY
 BY CARTEXT AS 'CAR'
 WHERE CAR LE 'F';
 ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END

APP FILEDEF CARHOLD DISK CARHOLD.FTM (APPEND

TABLE FILE CAR
 SUM SALES/D9
 BY COUNTRY
 BY TOTCARTEXT AS 'CAR'
 WHERE CAR LE 'F';
 ON TABLE SAVE AS CARHOLD
END

TABLE FILE CARHOLD
 SUM SALES AS 'Somme'
     PCT.SALES/D9.2% AS '%'
 BY COUNTRY
 ACROSS CAR AS ''
 ON TABLE COLUMN-TOTAL AS 'TOTAUX'
 ON TABLE PCHOLD FORMAT HTML
END

Try this,
DEFINE FILE CAR
TOTALSALES=SALES;
CNTRYSALES=SALES;
END
TABLE FILE CAR
SUM
TOTALSALES NOPRINT

SUM
CNTRYSALES NOPRINT
BY COUNTRY
SUM
SALES AS 'Somme'
COMPUTE PCT_SALES/D9.2% = SALES/CNTRYSALES*100; AS '%'

BY COUNTRY
ACROSS CAR AS ''
COMPUTE ROWTOT=C2;
ROWPCT=C2/C1*100;

IF CAR LE 'F'

ON TABLE COLUMN-TOTAL AS 'TOTAL'
END

Thanks everybody for your answers.

Dan, your solution is OK for the total column but not for each across column because each % column is 100% and that's not what I want.
For example, for W GERMANY SALES : for AUDI, SALES is equal to 7800 so I would have 7800/161390 = 4,83 %; for BMW, SALES is equal to 80390 so I would have 80390/161390 = 49.81 % - the total column of 88190 is OK (54,64%)


I've completed Dan's example to be like what I really need, with several fields for % calcultation and several BY.

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY

DEFINE FILE CAR
CARTEXT/A17 = ' ' | CAR ;
TOTCARTEXT/A17 = 'TOTAUX';
END

TABLE FILE CAR
SUM SALES/D9
DEALER_COST/D9
BY COUNTRY
BY SEATS
BY CARTEXT AS 'CAR'
WHERE CAR LE 'F';
ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END
APP FILEDEF CARHOLD DISK CARHOLD.FTM (APPEND

TABLE FILE CAR
SUM SALES/D9
DEALER_COST/D9
BY COUNTRY
BY SEATS
BY TOTCARTEXT AS 'CAR'
WHERE CAR LE 'F';
ON TABLE SAVE AS CARHOLD
END

TABLE FILE CARHOLD
SUM SALES AS 'Somme'
DEALER_COST AS 'Test'
PCT.SALES/D9.2% AS '%'
PCT.DEALER_COST/D9.2% AS '%'
BY COUNTRY
BY SEATS
ACROSS CAR AS ''
ON TABLE COLUMN-TOTAL AS 'TOTAUX'
ON TABLE PCHOLD FORMAT HTML
END

When you run this, the % are not calculated based on each total.
How do I do that ?

Thanks in advance for taking time to answer to my question.
Catherine

This message has been edited. Last edited by: Cati - France,

Here is the solution given by the french IBI support.
It's exactly what I need.
May interest some of you.
Catherine

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY

DEFINE FILE CAR
CARTEXT/A17 = ' ' | CAR ;
TOTCARTEXT/A17 = 'TOTAUX';
END

TABLE FILE CAR
SUM
COMPUTE SSALES/D12.2 = SALES ;
COMPUTE SDC/D12.2 = DEALER_COST ;

SUM SALES/D9
DEALER_COST/D9
BY COUNTRY
BY SEATS
BY CARTEXT AS 'CAR'
WHERE CAR LE 'F';
ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END
APP FILEDEF CARHOLD DISK CARHOLD.FTM (APPEND

TABLE FILE CAR
SUM
COMPUTE SSALES/D12.2 = 0 ;
COMPUTE SDC/D12.2 = 0 ;
SUM SALES/D9
DEALER_COST/D9
BY COUNTRY
BY SEATS
BY TOTCARTEXT AS 'CAR'
WHERE CAR LE 'F';
ON TABLE SAVE AS CARHOLD
END

TABLE FILE CARHOLD
SUM SALES AS 'Somme'
DEALER_COST AS 'Test'
COMPUTE PSALES/D9.2% = IF CAR NE 'TOTAUX' THEN ( SALES / SSALES ) * 100 ELSE PCT.SALES;
COMPUTE PDC/D9.2% = IF CAR NE 'TOTAUX' THEN ( DEALER_COST / SDC ) * 100 ELSE PCT.DEALER_COST;
BY COUNTRY
BY SEATS
ACROSS CAR AS ''
ON TABLE COLUMN-TOTAL AS 'TOTAUX'
ON TABLE PCHOLD FORMAT HTML
END

Or this:

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY

DEFINE FILE CAR
 CARTEXT/A17    = ' ' | CAR ;
 TOTCARTEXT/A17 = 'TOTAUX';
 TOTSALES/D9    = SALES ;
 TOTDLCOST/D9   = DEALER_COST ;
END

TABLE FILE CAR
 SUM TOTSALES
     TOTDLCOST
 SUM SALES/D9
     DEALER_COST/D9
 BY COUNTRY
 BY SEATS
 BY CARTEXT AS 'CAR'
 WHERE CAR LE 'F';
 ON TABLE HOLD AS CARHOLD FORMAT ALPHA
END

APP FILEDEF CARHOLD DISK CARHOLD.FTM (APPEND

TABLE FILE CAR
 SUM TOTSALES
     TOTDLCOST
 SUM SALES/D9
     DEALER_COST/D9
 BY COUNTRY
 BY SEATS
 BY TOTCARTEXT AS 'CAR'
 WHERE CAR LE 'F';
 ON TABLE SAVE AS CARHOLD
END

TABLE FILE CARHOLD
 SUM SALES AS 'Somme'
     COMPUTE PCT_SALES/D9.2%  = SALES / TOTSALES * 100 ; AS '%'
     DEALER_COST AS 'Test'
     COMPUTE PCT_DLCOST/D9.2% = DEALER_COST / TOTDLCOST * 100 ; AS '%'
 BY COUNTRY
 BY SEATS
 ACROSS CAR AS ''
 ON TABLE COLUMN-TOTAL AS 'TOTAUX'
 ON TABLE PCHOLD FORMAT HTML
END