How can i hide Div/0 Error in KPI

Question & Answer
, , , ,
1

Hi Everyone,

I am facing issues of Div/0 in KPI visual.

when the value for current month is 0 is there a way that i can hide div0 error

div0_issue

please suggest ways to achieve this.

Thanks,
Divya

2

Can anyone please suggest ways to Hide to handle this issue

3

Hi, you may want to specially treat the KPI function using ifelse for the case when the value for the month is 0, something like ifelse(value=0, “N/A”, function).

4

I think you could replace the field you currently have in the Target Value Field Well with a calculated field as follows:

  ifelse(targetValue=0, null, value)
5

Hi @darcoli, @emilyzhu ,
Thanks for your response on my issue.

Handling the KPI visual with IFelse(value=0,null,value) solves the issue partially, but still there is major issue.
Few scenarios-
If both values are 0 - result with the function gives - NO Data for the entire visual.

Case1

If the recent week is 0 and prior week has some data - It displays No data for the current week value and prior week with the data that is available, but the difference Percentage shows an error cannot Compare Values

case2

where as this was computing correct if it was not handled with ifelse
Case3

If there is just a way to handle Comparison Format.
case4

Thanks,
Divya

6

You should use that calculated field only in the Target Value Field Well (ie previous month only)

image
image916×134 2.65 KB

Another way around this could be to use the following calculated field in the Target Value instead (and keep the fields’ formatting not to show decimal places):

ifelse(sum(value)=0, 0.0001, sum(value))
7

This way does not works either, Setting a very low value results in incorrect results as there is many data to the scenario which i am trying to handle.

And adding the new calculated field in the target value it does not allow to take another dimension in the Trend Group.

8

In the last calculated field, you would only be setting the “low value” in the final sum not for the individual rows - so amount of rows should not matter. Also, you would use this only as a workaround for the KPI visual. I tried it and it seems to work well. Do you have any specific use case it does not cover?

9

Create a Calculated Field with something like this in it:

ifelse(CurrentMonth <> 0, MyVALUE / CurrentMonth, 0)

That will look at CurrentMonth and if it is not 0 it will return MyVALUE / CurrentMonth. If Current Month is zero then it will return zero.

You don’t say what you are trying to divide by CurrentMonth so simply replace MyVALUE with the value you need.
In the KPI replace whatever value you have in there with the Calculated Field.

10

Hi @darcoli ,

Even if update low value to the final sum in the KPI visual, the result When the displayed as in difference as percent it results a very high and incorrect value.
Issue1

Formula Used- ifelse(sum({amount})=0,.00001,sum({amount}))
issue3

Results are correct as in Months in KPI but the comparison as difference percent is not correct.

And if in case i use Null, then the dimension with is set in trend group is missing.
Issue2

Please let me know if i am missing any step.

Thanks,
Divya

11

Hi,

use this code.
ifelse(value=0,1,value)

this will return 1 in case of 0 and divided by 1 will not impact on you value :slight_smile:

12

Hi @Naveed ,

I am facing similar issue, however for this case, current month has a value of 1 whereas previous month has a value of 0. In this scenario the coercing of the 0 to 1 does not work. Is there any other way you think this can be worked out?

Thanks,
Prantika

13

this worked well for my use - thank you @darcoli!