Datakat
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
The above shows the page with the weird results.
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
The above shows the page with the weird results.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Apparently I'm and idiot, and fat fingered something while checking answers...... Thank you for setting me straight.
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_CorrectedNetInco me}
-{@B_MTD_CorrectedNetIncom e})
%abs({@B_MTD_CorrectedNetI ncome})
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.
Formula looks like this:
if {@B_MTD_CorrectedNetIncome
then ({@CY_MTD_CorrectedNetInco
-{@B_MTD_CorrectedNetIncom
%abs({@B_MTD_CorrectedNetI
else 0
if B_MTD_CorrectedNetIncome= -20786.00 (loss of 20786.00)
and CY_MTD_CorrectedNetIncome=
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.
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_LubeMarginC PG})
-ABS({@YTD1_B_LubeMarginCP G}))
%abs({@YTD1_B_LubeMarginCP G})
ELSE ({@YTD1_CY_LubeMarginCPG}
-{@YTD1_B_LubeMarginCPG})
%abs({@YTD1_B_LubeMarginCP G})
if {@YTD1_B_LubeMarginCPG} <> 0
THEN IF ({@YTD1_CY_LubeMarginCPG}<
{@YTD1_B_LubeMarginCPG}<0)
then (ABS({@YTD1_CY_LubeMarginC
-ABS({@YTD1_B_LubeMarginCP
%abs({@YTD1_B_LubeMarginCP
ELSE ({@YTD1_CY_LubeMarginCPG}
-{@YTD1_B_LubeMarginCPG})
%abs({@YTD1_B_LubeMarginCP
What results are you expecting?
What are the values of Sum ({@CY_MTDSum}, {@Line Items Grouping}) and Sum ({@LY_MTDSum}, {@Line Items Grouping}) ?