How to handle Div/0 Error in KPI

[rahul.kolage]

Hi
How to handle Div/0 error in KPI?
I have tried solutions provided in https://community.amazonquicksight.com/t/how-can-i-hide-div-0-error-in-kpi/3465

But these are partial solutions.

I have to show comparison between Yesterday’s Sales and Day Before Yesterday’s Sales
Case 1 : Both Sales values are 0
Yesterday’s Sale = 0
Day Before Yesterday’s Sales = 0
Output : Div/0 error

Case 2 : Try to set Day Before Yesterday’s Sales = 0.001 ( as per suggestion provided in article )
Output: No error
This solution looks fine. BUT consider case, for example, if
Yesterday’s Sales = 50
Day Before Yesterday’s Sales = 0.001
The Difference as percent.(%) value will be incorrect.

Case 3 : Try to set Day Before Yesterday’s Sales = 1 ( as per accepted solution in article )
Output: No Div/0 error, error got handled , but it shows my “Day Before Yesterday’s Sales = 1”
which is incorrect as on that day no Sales happened, still we are showing 1.

KPI Visual should have more ways to control “Difference as percent” output or other parts of KPI block
Is there any other way to handle this issue along with all possible cases?

[neelay]

@rahul.kolage
can’t you do a custom field with a formula similar to below and use that for KPI? so actual sale value is not affected.

change_in_sale = YD - DBYD

if YD == DBYD: 
    DBYD = 1

PCTCH = (change_in_sale * 100) / DBYD