Presentation of percentages when values are near zero

Hello,

We have a user who questioned a percentage calculation. Based on the filtering of her data she has current year sales of 136602 and prior year sales of -797 (due to some credits from 2016). The percent variance is -17,234% and although I have explained that it is mathematically correct she would prefer, I believe, that such extremes be hidden somehow, similar to how Board does not show the percent when prior year is zero. Any ideas?

Thank you,

Peter

Find more posts tagged with

Accepted answers

All comments

Björn Reuber

Hi,

maybe you can use the Algorithm Round to eliminate values near zero

Or a different BOARD Layout Algorithm Syntax will fit for your case

As an alternative you can define a certain algortihm to decide, which values should be shown and which not.

Regards

Björn

unknown

Extremes do look odd in comparison to nearby values. As Björn Reuber suggested, an algorithm may be a good fit here. I would suggest something like the following.

Block A = Current Year Sales
Block B = Prior Year Sales
Block C = IF ( AND ( B>0, A/B>0) , A/B*100 , 0)

By using the IF function and the AND function together, we can make the column algorithms more precise to handle odd cases more elegantly.

Peter DeGregorio

Thank you for the link to algorithm functions

Peter DeGregorio

Thank you. Expanding on your solution to suit my purpose:

Block A = Current Year Sales
Block B = Prior Year Sales
Block C = (a-b)/b*100 (The current formula … Now to be hidden)
Block D = if(a-b>=0,if(abs(c)<1000,abs(c),999.9),if(abs(c)<1000,-abs(c),-999.9)) (Alert green >=0, Red < 0)

Shows positive % if there is growth from prior year to current year otherwise negative percent, regardless of A or B is positive or negative as may happen in our business). Limits % shown to 999.9 plus or minus.