Conditional Calculated Field Showing #Error

Question

I am trying to display a Percentage value in a Calculated conditional Files. Below is the Expression

=IIF(Sum(Fields!issued.Value)=0,0,Sum(Fields!FrstOutConv.Value)/Sum(Fields!issued.Value))

Since I am dividing issued Field I want to ensure the value is not 0. In seen screen grab you can see it works but instead of showing 0 it Shows #Error when issued field is 0 Image of Fields Everything works if I can only show the #Error as 0. As you can see the field Issued has 0 in it not Null or "" Thanks for any help Rich V

Answer 1

This is a common mistake...

IIF evaluates all parts of the expression every time, so even when your issued is 0 and the 2nd part of the IIF will never be returned, the Sum(Fields!FrstOutConv.Value)/Sum(Fields!issued.Value) part is still evaluated.

You can fix this by using the following.

Instead of

=IIF(
    Sum(Fields!issued.Value)=0
    ,0
    ,Sum(Fields!FrstOutConv.Value)/Sum(Fields!issued.Value)
    )

use this

=IIF(
    Sum(Fields!issued.Value)=0
    ,0
    ,Sum(Fields!FrstOutConv.Value)/IIF(Sum(Fields!issued.Value)=0, 1, Sum(Fields!issued.Value))
    )

All this does is force the division to divide by 1 if 'issued is zero, which is OK because that bit will never be returned if 'issued is zero.