In Webi formulas which contain a divide by zero will return #DIV/0. Similarly, formulas which have a blank denominator will incorrectly return (100.0%). An example is depicted in the screen shot below. As you could see the highlighted Order ID’s have an incorrect Volume Variance.
Here is how to correct this anomaly.
STEP 1] Create a dimension called [*Null Value] and paste the following If statement within it:
STEP 2] revise the formula which is resulting in the divide by zero (or divide by blank) error as following.
For example, if the formula is: [VOLUME CY]/[VOLUME PY]-1
Then add an IF statement that would return the [* Null Value] whenever the denominator is either Null or equal to zero. Just use the if statement below and replace the part highlighted in yellow with the denominator and the part highlighted in green with your main original formula.
STEP 3] right click on the volume variance column and select “Format Number”. Then click on “Custom”
Note: please ensure that the preferences are set at Applet before performing this step.
STEP 4] Input the values listed below for “Positive”, “Negative”, “Equal to “Zero”, and “Undefined”. And then click on add.
STEP 5] select the new custom format and click on “Ok” AS illustrated above.
As you could observe in the screenshot above, all of the divide by zero and divide blanks are now illustrated with *** and this will not only create consistency but will help avoid unwanted #DIV/zero and false (100.0%) values.