none
Divide By 0 Error on Lookup RRS feed

  • Question

  • Hello,

    Running into a simple divide by 0 issues I cannot seem to workaround. I have a lookup in my expression, can anyone offer suggestions how to build this

    In the past I have used custom code such as:

     Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
            If second = 0 Then
                Return 0
            Else
                Return first / second
            End If
        End Function

    This does not seem to work with my lookup. 

    My expression is:

    =SUM(Lookup(Fields!pTrans.Value, Fields!pTrans.Value, Fields!salesCurYrValue.Value, "CurrentYr_DS")-Fields!salesPrevYrValue.Value)/Fields!salesPrevYrValue.Value

    Thanks

    Monday, July 16, 2018 7:14 PM

All replies

  • you can still do this

    =IIF(Fields!salesPrevYrValue.Value > 0,SUM(Lookup(Fields!pTrans.Value, Fields!pTrans.Value, Fields!salesCurYrValue.Value, "CurrentYr_DS")-Fields!salesPrevYrValue.Value)/IIF(Fields!salesPrevYrValue.Value > 0,Fields!salesPrevYrValue.Value,1),0)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 16, 2018 7:20 PM
  • Hi GiracBa,

    In your scenario, you use the custom code which is used for denominator is 0. But in your expression you didn't use this function. You could try to use =code.Divide(SUM(Lookup(Fields!pTrans.Value, Fields!pTrans.Value, Fields!salesCurYrValue.Value, "CurrentYr_DS")-Fields!salesPrevYrValue.Value),Fields!salesPrevYrValue.Value) to see whether it works or not.

    Or you could use expression like below instead of using custom code

    =iif(Fields!salesPrevYrValue.Value=0,0,SUM(Lookup(Fields!pTrans.Value, Fields!pTrans.Value, Fields!salesCurYrValue.Value, "CurrentYr_DS")-Fields!salesPrevYrValue.Value)/iif(Fields!salesPrevYrValue.Value=0,1,Fields!salesPrevYrValue.Value))

    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 17, 2018 1:38 AM
    Moderator