Registered: 1346236908 Posts: 1
Reply with quote #1
I have a question that has become somewhat of an internal debate among my team and after spending about a half day researching I see allot of opinions on various internet sites but no clear 'standard'. I was wondering if you would weigh in on the best solution.
We use a classic percent change calculation: (new_value-original_value)/(original_value) for our retail reporting. This formula is impacted by negative numbers which produce a mathematically correct answer but it can also be misleading to a business user. For example TY=1- and LY=-5 results in -300% but the user is expecting to see 300%.
We are considering changing to (new_value-original_value)/ABS(original_value) which would eliminate the sign issue. But some math purists on our team are complaining saying it's no longer a real % Change so if we do that we must change our definition.
The other issue of course is when the baseline is zero. e.g. a new product that has zero units sold last year and 500 units sold this year. Mathematically this results in div/0 which of course is correct since you technically have infinite change but again the business user is expecting to see a number indicating growth. Math purists on my team say there is no solution to this problem.
Is there a solution that balances what the business user expects to see and will find useful vs. what will make the mathematics crowd happy?
Registered: 1274885665 Posts: 7
Reply with quote #2
Thought I would offer an opinion on this for what it is worth. I would expect the + or - sign in the percentage change to be rather important as it tells us whether the change was in the right direction, I guess I don't understand why if the new value is less than the original and results in -300% that would be misleading? In terms of a zero baseline, I would argue that div/0 is accurate and I would be loathe to mess with the maths. What I would suggest though is that you can present this differently in the report. When you say a new product would you not be able to present 'No previous data' in the change column? I think it would be important to have an actual value column in this type of report anyway. Attached Images
Registered: 1306510245 Posts: 51
Reply with quote #3
Probably not the response you're looking for, but have you considered just showing the actual variance rather than a percentage? Showing the increase/decrease of the new value from the original value seems to me a more consistent representation of the change and doesn't suffer from any of the problems that calculating percentages does. Is there a reason percentages are so important to the user?
If percentages are necessary for the user (this probably depends on their workflow), I'm afraid that the math purists may be right. The impact of negative numbers is that you can't calculate a percentage. If you have -5 last year and 10 this year, calling that a change of -300% or +300% would be misleading. To refer to my earlier point, calling it an increase of 15 would be more accurate.
As for the zero baseline, it would be better to show a message such as "No previous data" as mthomas1973 suggested, since any percentage you could possibly show would be incorrect and of no real value to the user.
Registered: 1155665203 Posts: 69
Reply with quote #4
Ask your maths purists how they feel about vector division. They should reply that vector division is an undefined operation. Then point out to them that by dividing the change (with a plus/minus) by the original, also with a plus/minus, they are attempting to divide a vector by a vector. It makes more sense to divide a vector by a scalar, which is what you're doing when you take the absolute magnitude of the original value: you're quite properly throwing away the direction of the denominator, because all you're interested in is its size,
Registered: 1306510245 Posts: 51
Reply with quote #5
Ask your maths purists how they feel about vector division. They should reply that vector division is an undefined operation. Then point out to them that by dividing the change (with a plus/minus) by the original, also with a plus/minus, they are attempting to divide a vector by a vector.
Only say this to them if you want to get laughed at. Just because a number can be positive or negative does not make it a vector, and reversing the sign (based on the condition of what the sign was originally, I might add) is not the same as throwing away a vector's direction. Scalars can be negative.