Answered by:
If then Formula
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
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. Marked as answer by Challen FuModerator Sunday, May 16, 2010 6:38 AM

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! :) Marked as answer by Challen FuModerator Sunday, May 16, 2010 6:38 AM

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
 Marked as answer by Challen FuModerator Sunday, May 16, 2010 6:38 AM

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. Marked as answer by Challen FuModerator Sunday, May 16, 2010 6:38 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. Marked as answer by Challen FuModerator Sunday, May 16, 2010 6:38 AM


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! :) Marked as answer by Challen FuModerator Sunday, May 16, 2010 6:38 AM

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
 Marked as answer by Challen FuModerator Sunday, May 16, 2010 6:38 AM

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. Marked as answer by Challen FuModerator Sunday, May 16, 2010 6:38 AM