Discussion Board for collaboration related to QlikView App Development.
Was writing a report today analysing gross profit for a customer this year v last year. Simple formula (this years GP / last years GP) -1.
Customers figures were 987 gp this year and -175 gp last year.
This meant that I was dividing a positive by a negative and ended up with a negative percentage when in fact gp has increased.
Have been playing around with the "fabs" function to convert the last year gp figure but am unsure if this is the correct way to report this...
How do other users handle this type of situation. Had a quick look on the internet and found some suggestions that you should not report any percentage where the previous years trading figure was a loss.
Example below..
http://mathforum.org/library/drmath/view/55720.html
Would be interested to know how other users tackle this situation...
Regards
Paul
GP increased, but the percent change WAS -564%. I agree that that would likely confuse the users, but it doesn't mean that 564% is the correct answer. I agree with not reporting it. After all, reading just the % would make you think that the change from -175 to 987 is no better than a change from 175 to -987. Both represent the same percent increase, but that makes the numbers useless for comparison.
What it indicates to me, really, is that you've chosen the wrong way to represent improvements given your data set. If you can have very small or negative numbers, percent improvements become fairly meaningless. You COULD restrict your measurement to only where the percent improvement is meaningful, but that leaves some of your data set unanalyzed. Perhaps there's a better measure.
Reading your link now... yeah, reporting P or L when dealing with negatives (or zero) seems like a good enough approach to me if you can't think of a better way to represent improvements to profitability.
Yeah, there's a similar answer to mine, "If your business plan has profits that vary so widely that some are negative, then I would question the utility of percentages."
I've not had to report this sort of data. I'd probably discuss it with accounting if I had to.
I found this post, and suggests the following as a solution:
If the 1st number is negative, but not the second, use Absolute Values after you subtract.
If the 2nd number is negative, but not the first, multiply by -1 when you're done.
If both the numbers are positive solve normally - The answer is positive if first number is smaller than the second number, negative if the other way.
If both the numbers are negative solve normally - The answer is positive if the first number is more negative than the second number, negative if the other way.
Hope this helps.
Mike
Thanks John and Miguel...
I am going to take John's advise and discuss with our accounting department and will pass on your suggestion Miguel.
Miguel Garcia wrote:
I found this post, and suggests the following as a solution:
I tried following the instructions from the linked post to the letter. As best I can tell, the results are exactly the same as the common expression, ThisPeriod/PrevPeriod - 100%. So as best I can tell, it was an exercise in mathematical obfuscation, not a solution. I could have misunderstood the suggestion, of course.
Hello John,
I made the excercise and, following the instructions, got a different result than the "common expression" (ThisPeriod/PrevPeriod - 100%). Let me do it step by step here:
To follow the same line of thought as the original question, we define:
If the 2nd number is negative, but not the first, multiply by -1 when you're done."
(987/(-175) - 100%) * -1 = 6.64
The first result would indicate an "improvement" of -664% (decrease), while the second result indicates an improvement of 664% (improvement indeed).
This means that the final value is 6.64 times higher than the initial value (absolute). So, 175 * 6.64 + (-175) = 987
Makes sense to me.
Mike.
OK, perhaps it's a matter of what we interpret as "first number" and "second number". If I assume "first number" is the previous period and "second number" is the current period, then I get the common result. If I assume the opposite, then am merely reversing the sign. See attached spreadsheet with both assumptions and comparison of results. If you're reading the instructions from the post differently than I am, let me know.
As for -175 to 987 being a 664% improvement, it quite simply is not. 987 is a 664% improvement from 129.2. Improving from -175 to 987 is NOT the same as improving from 129.2 to 987. For that matter, improving from 0 to 987 is an infinite percent improvement, yet still isn't as good as improving from -175 to 987. So we need to report, what, something greater than an infinite percentage? Percent improvement is simply not a reasonable way to report on data that can change sign, or for that matter, be close to zero.
Hello!
Here are my thoughts on the topic:
Best Regards,
Mike.
Message was edited by: Miguel Garcia Bullet point #4: Results of zero and one are obtained when both numbers are of equal sign. When they are of different sign, the common expression does not handle it as the "new" expression, and that's exactly the matter of this discussion. But the only difference is a switch in the sign.