Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

This Year v Last Year Percentage

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

7 Replies
Highlighted
MVP
MVP

This Year v Last Year Percentage

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.

Highlighted
Luminary
Luminary

Re: This Year v Last Year Percentage

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

Miguel García
Qlik Expert, Author and Trainer
Highlighted
Specialist
Specialist

This Year v Last Year Percentage

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.

Highlighted
MVP
MVP

Re: This Year v Last Year Percentage

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.

Highlighted
Luminary
Luminary

Re: This Year v Last Year Percentage

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:

  • 987: as the final value (first number)
  • -175: as the Initial value (second number)

    • The "common expression" results in:
      • 987/(-175) - 100% = -6.64
    • Following the suggested procedure:
      • "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.

Miguel García
Qlik Expert, Author and Trainer
Highlighted
MVP
MVP

Re: This Year v Last Year Percentage

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.

Highlighted
Luminary
Luminary

Re: This Year v Last Year Percentage

Hello!

Here are my thoughts on the topic:

  • The procedure provided on the post is something to be taken as specific instructions to follow, not something subjective in which you can assume A instead of B, etc. I know it is not extensive in its explanation, but then again it is just a forum post.
  • The original formula to calculate a Delta % is, as far as I know, (Final Value - Initial Value) / (Initial Value), which can then be simplified as (Final Value / Initial Value) - 1.
  • Taking the previous point, we identify "First number" as "The first number that you use in the formula", which is Final value. And "Second Numer" becomes "The second number you use in the formula", which is the initial value.
    • Therefore, it is not a matter of interpretation, but rather a matter of the original explanation provided, which lacks clarity maybe. But once  we have settled on the correct use of the recipe, it becomes easier to follow.
  • I noticed, in your spreadsheet, that Cases 3 and 4 are not being "solved normally" as the instructions suggest. I mean, your are using "IF(AND(A4>=0,B4>=0),IF(A4<B4,ABS(E4),-ABS(E4)))" when it should pass the result without modification: "IF(AND(A4>=0,B4>=0),E4)"
    • If you change your formula to "solve normally", you will get (for when both numbers are positive or both numbers are negative) only differences of zero or 1 in your "common vs new" column. Zero means, well, the result was zero for both, and one means the result was the same for both (not necessarily zero).
  • As for the "what-should-be-reported" discussion, I will not comment on it. My aim here is to provide technical advice or, in this case, an approach to accomplish something, not my business/consultant/personal opinion.

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.

Miguel García
Qlik Expert, Author and Trainer