none
If then Formula RRS feed

  • Question

  • I have the attached formula to create a % of a total number. My problem is, if dividing by 0, I get an error. i thought I had it correct but apparently I do not.

    =IIF((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))=

    0,0,((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))/((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value)))))

     

    Any help to get the error to stop and show 0 would be appreciated

    Monday, May 10, 2010 7:13 PM

Answers

  • In some cases, you have to check to see if it equals 0 twice... once initially, and then once again when referring to the denominator.  Try this and see if it helps you more:

    IIF((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))=0,0,((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))/IIF((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))=0,1,(Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value)))))

    (I hope I got the number of parentheses right...)



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    Monday, May 10, 2010 7:20 PM
  • Add a condition to check if the denominator is NULL also along with zero, something like

    =IIF((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))=

    0 or isnothing(Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value)),0,((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))/((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value)))))

     Another doubt, isn't the numerator and the denominator same, which means you will get 1 always if it is not zero or NULL? Or maybe my sleepy eyes must be playing some tricks on me


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Monday, May 10, 2010 8:21 PM
    Moderator
  •  Hi Kate,

    Just as Jason mentioned, value of the expression (Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))/(Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))  is always equal to 1 ,please make sure the expression correct. After that click the textbox of total number and press “F4” button to find out the Format Property in the Property window in the right side of BIDS. Type in P0 to format the number like a% just as you said.

    If you have any question, please feel free to ask.

    Regards,

    Challen Fu

    Thursday, May 13, 2010 3:25 AM
    Moderator
  • Hello,

    Just another way of writing the expression, you can use this or any of the above

    =FORMAT(IIF((Sum(Val(Fields!AGINGBK1.Value & "")) + Sum(Val(Fields!AGINGBK2.Value & ""))) = 0, 0, 1) ,"P0")

    Hopt its helpful...


    Pavan Kokkula Infosys Technologies Limited.
    Thursday, May 13, 2010 3:36 AM

All replies

  • In some cases, you have to check to see if it equals 0 twice... once initially, and then once again when referring to the denominator.  Try this and see if it helps you more:

    IIF((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))=0,0,((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))/IIF((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))=0,1,(Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value)))))

    (I hope I got the number of parentheses right...)



    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    Monday, May 10, 2010 7:20 PM
  • Aaron has already answered your question.

    Just remember for any % calculations or divisions, always check Denominator if it is greater than 0.

    IF denominator > 0.0  DO calculations ELSE 0 (or don't do calculations)
    Monday, May 10, 2010 8:17 PM
  • Add a condition to check if the denominator is NULL also along with zero, something like

    =IIF((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))=

    0 or isnothing(Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value)),0,((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))/((Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value)))))

     Another doubt, isn't the numerator and the denominator same, which means you will get 1 always if it is not zero or NULL? Or maybe my sleepy eyes must be playing some tricks on me


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Monday, May 10, 2010 8:21 PM
    Moderator
  •  Hi Kate,

    Just as Jason mentioned, value of the expression (Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))/(Sum(Fields!AGINGBK1.Value)+Sum(Fields!AGINGBK2.Value))  is always equal to 1 ,please make sure the expression correct. After that click the textbox of total number and press “F4” button to find out the Format Property in the Property window in the right side of BIDS. Type in P0 to format the number like a% just as you said.

    If you have any question, please feel free to ask.

    Regards,

    Challen Fu

    Thursday, May 13, 2010 3:25 AM
    Moderator
  • Hello,

    Just another way of writing the expression, you can use this or any of the above

    =FORMAT(IIF((Sum(Val(Fields!AGINGBK1.Value & "")) + Sum(Val(Fields!AGINGBK2.Value & ""))) = 0, 0, 1) ,"P0")

    Hopt its helpful...


    Pavan Kokkula Infosys Technologies Limited.
    Thursday, May 13, 2010 3:36 AM