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
Answers
-
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
2 -
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.
1 -
Thank you for the link to algorithm functions
1 -
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.
1
