Link to home
Start Free TrialLog in
Avatar of Datakat
DatakatFlag for United States of America

asked on

Calculate percent change when denominator is negative

I have this formula in my Crystal Reports 2008.  Usually it works fine, but if Sum ({@LY_MTDSum}, {@Line Items Grouping}) is a negative number, it doesn't calculate correctly .
If Sum ({@LY_MTDSum}, {@Line Items Grouping}) <> 0
then (Sum ({@CY_MTDSum}, {@Line Items Grouping})  
      -Sum ({@LY_MTDSum}, {@Line Items Grouping}))
       /Sum ({@LY_MTDSum}, {@Line Items Grouping})*100

else 0
User generated image
The above shows the page with the weird results.
Avatar of UnifiedIS
UnifiedIS

I can't tell what is "weird". A negative percentage seems reasonable to expect when comparing year to year.
What results are you expecting?
What are the values of Sum ({@CY_MTDSum}, {@Line Items Grouping})   and Sum ({@LY_MTDSum}, {@Line Items Grouping})  ?
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Datakat

ASKER

Apparently I'm and idiot, and fat fingered something while checking answers...... Thank you for setting me straight.
Avatar of Datakat

ASKER

I revisited this issue while re-writing this old report. I may not have been clear enough when I first asked this question.  In fact, the percent of change SHOULD be positive.  220.04 is much more than -3.41.  I did a little thinking, and a little research online and discovered that if one uses absolute value for the divisor, the math works.
Formula looks like this:
if {@B_MTD_CorrectedNetIncome} <> 0
          then ({@CY_MTD_CorrectedNetIncome}
            -{@B_MTD_CorrectedNetIncome})
        %abs({@B_MTD_CorrectedNetIncome})
else 0

if B_MTD_CorrectedNetIncome= -20786.00 (loss of 20786.00)
and CY_MTD_CorrectedNetIncome=6620.45 (income of 6620.45)
the percent change should be positive, as CY_MTD is a great increase over B_MTD.  Using the above formula, and using absolute value of B_MTD give 131.9%.  This is the correct answer for this case.
Avatar of Datakat

ASKER

And another issue with this: If both amounts are negative, and I am using %abs, then the answer, while arithmetically correct, doesn't reflect what the user wants to see from this report.  This can happen often, as this report using general ledger accounts and expenses usually have a negative value for their summary. If the negative amount is greater than the prior negative amount, this should show as an increase, or positive %. So my formula now looks like:

if {@YTD1_B_LubeMarginCPG} <> 0
THEN IF ({@YTD1_CY_LubeMarginCPG}<0 AND
            {@YTD1_B_LubeMarginCPG}<0)
     
 then (ABS({@YTD1_CY_LubeMarginCPG})
            -ABS({@YTD1_B_LubeMarginCPG}))
        %abs({@YTD1_B_LubeMarginCPG})

ELSE ({@YTD1_CY_LubeMarginCPG}
            -{@YTD1_B_LubeMarginCPG})
        %abs({@YTD1_B_LubeMarginCPG})