Getting NaN or Infinity on divisions. How to check for both?

Question

User1984700640 posted

I presently have this code which fixes my "Infinity" problem:

=IIF((Count(Fields!bk_id.Value) -
Sum(Fields!ExitSale.Value))/
(Count(Fields!it_id.Value) - Count(Fields!tour_date.Value) - Count(Fields!it_cancel_date.Value)) = "Infinity", 0,
(Count(Fields!bk_id.Value) -
Sum(Fields!ExitSale.Value))/
(Count(Fields!it_id.Value) - Count(Fields!tour_date.Value) - Count(Fields!it_cancel_date.Value)))

But sometimes I also get NaN as my result.  How can I change the above code to display 0 instead of NaN but still keep my "Infinity" situation working?

Answer 1

User-734925760 posted

Hi,

So far as I know, if you want to display 0 as the value is NaN, you need to check if this value is NaN. You can try to add case condition in the code, and use the isNaN() function to check if this value is NaN.

For more information about NaN, please refer to the link below:

http://en.wikipedia.org/wiki/NaN

Hope it's useful for you.

Best Regards,

Michelle Ge

Answer 2

User-830595639 posted

Hi,

You need to check your below part.

IIF((Count(Fields!it_id.Value) - Count(Fields!tour_date.Value) - Count(Fields!it_cancel_date.Value))=0,1,(Count(Fields!it_id.Value) - Count(Fields!tour_date.Value) - Count(Fields!it_cancel_date.Value))).

Generally the reason for NaN is divided by 0. If above code doesn't work then you can try putting one more IIF on top of your existing expression.