Hi,

Can someone explain me why all the percentage calculations in report painter are good until it takes a negative input value from the data and the percentage result become the difference. At first, it looks like the negative value is recognised as positive. Example:
Margin% = sales - costs / sales * 100

Sales = -108.06
Costs = 85.07

If I do the calculation myself, it gives 178.72%.
Report painter gives me 21.28% as it took Sales input as a positive value.

What am I missing? Thanks you.

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

Marikaki, this could be related to the order of precedence of the operators involved.

Can you change your expression to:

margin% = (sales - cost) / sales * 100; 

That, on paper, gives me 178.72% as you expect.

- Neftali.

Could you also post the data type and length of all of the fields involved in the calculations? (SALES, COSTS, MARGIN)? There could be something else going on there.

Hi Neftali,

Please find the defininition in the code. I tried with parentheses and it did not work much.
The last 3 lines are about the margin% before the ''BY''.

Thank you.

 
DEFINE FILE TMPHOLD
 CURR_EXTPRICE/D16&FORMAT= IF DATEYR EQ &CURYR THEN CURSALES ELSE 0;
 LSTY_EXTPRICE/D16&FORMAT= IF DATEYR EQ &LSTYR THEN CURSALES ELSE 0;
 L2YR_EXTPRICE/D16&FORMAT= IF DATEYR EQ &LST2YR THEN CURSALES ELSE 0;
 CURR_EXTWAC/D16&FORMAT  = IF DATEYR EQ &CURYR THEN CURCSTSO ELSE 0;
 LSTY_EXTWAC/D16&FORMAT  = IF DATEYR EQ &LSTYR THEN CURCSTSO ELSE 0;
 L2YR_EXTWAC/D16&FORMAT  = IF DATEYR EQ &LST2YR THEN CURCSTSO ELSE 0;
-*
 CURRYTD/D16&FORMAT= IF DATEYR EQ &CURYR  THEN YTD&MEAS ELSE 0;
 LSTYTD/D16&FORMAT = IF DATEYR EQ &LSTYR  THEN YTD&MEAS ELSE 0;
 LST2YTD/D16&FORMAT= IF DATEYR EQ &LST2YR THEN YTD&MEAS ELSE 0;
-*
 LSTYR/D16&FORMAT = IF DATEYR EQ &LSTYR  THEN CUR&MEAS ELSE 0;
 LST2YR/D16&FORMAT= IF DATEYR EQ &LST2YR THEN CUR&MEAS ELSE 0;
END

TABLE FILE TMPHOLD
SUM  CURRYTD AS '&CURYR YTD Ventes/Sales'
     LSTYTD  AS '&LSTYR YTD Ventes/Sales'
     LST2YTD AS '&LST2YR YTD Ventes/Sales'

     COMPUTE VARLSYR/D16&FORMAT = CURRYTD - LSTYTD;  AS '&CURYR Variance'
     COMPUTE VARLS2YR/D16&FORMAT= LSTYTD  - LST2YTD; AS '&LSTYR Variance'

     LSTYR   AS '&LSTYR Ventes/Sales'
     LST2YR  AS '&LST2YR Ventes/Sales'

     COMPUTE MARGE_PCT/D16.2% = (CURR_EXTPRICE - CURR_EXTWAC) / CURR_EXTPRICE * 100; AS '&CURYR Marge/Margin'
     COMPUTE MARGE_LYR/D16.2% = (LSTY_EXTPRICE - LSTY_EXTWAC) / LSTY_EXTPRICE * 100; AS '&LSTYR Marge/Margin'
     COMPUTE MARGE_L2Y/D16.2% = (L2YR_EXTPRICE - L2YR_EXTWAC) / L2YR_EXTPRICE * 100; AS '&LST2YR Marge/Margin'

DR_REG NOPRINT
BY HIGHEST TOTAL &RANK NOPRINT
BY &DSC
BY &DIM NOPRINT
BY &COL1
BY &COL2
BY &PER
HEADING
etc......

May be you need this information for &format:

 
-SET &FORMAT=IF &MEAS EQ 'SALES' OR 'CSTOTH' OR 'CSTSO' OR 'CSTWAC' THEN 'M' ELSE ' ';

We will need to know what value '&FORMAT' has since it seems to be affecting the data type definition of most columns. Could you also please post your code using the (CODE) tags?

Merci,
- Neftali.

Great! You answered before I asked

quote:

Sales = -108.06
Costs = 85.07

Marikaki, where are you obtaining those values from?

Your field definitions are all D16 (without decimals). Anyway, that may not be that important; what matters is that I simulated your MARGE_PCT calculation using the CAR table and it does provide correct results even when dealing with negative numbers. Adding/removing currency support from the data type definitions did not affect the calculations. Please take a look at FRANCE in the code below:

TABLE FILE CAR
SUM 
COMPUTE CURR_EXTPRICE/D16.2M = IF COUNTRY EQ 'FRANCE' THEN -108.06 ELSE SALES; AS 'Sales'
COMPUTE CURR_EXTWAC/D16.2M = IF COUNTRY EQ 'FRANCE' THEN 85.07 ELSE SEATS * 200; AS 'Costs'
COMPUTE MARGE_PCT/D16.2% = (CURR_EXTPRICE - CURR_EXTWAC) / CURR_EXTPRICE * 100; AS 'Margin'
BY COUNTRY
END

Could you replicate a similar exercise with your data hopefully using a single BY field? Something like:

TABLE FILE TMPHOLD
SUM CURR_EXTPRICE
    CURR_EXTWAC
COMPUTE COMPUTE MARGE_PCT/D16.2% = (CURR_EXTPRICE - CURR_EXTWAC) / CURR_EXTPRICE * 100;
BY <something>
END

By looking closely at the data that is really being used you might be able to spot what's going on.

- Neftali.

Neftali,

Well, I am a little bit because you were right: Looking closely to the data I noticed that when I copied-paste the data to do seperate calculations in Excel, I didn't selected the minus sign of my second value(curr_extwac) which gives a different percentage.

Sorry about that.
Have a nice week-end.
Merci encore!

Pas de problème! I'm glad you found what the issue was. Could you please change the topic's title to [CLOSED]?

Nice weekend to you too!

- Neftali.