Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Division by Zero Error Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, June 27, 2013 1:31 PM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, March 04, 2014 11:09 AM Points: 256, Visits: 587
 Can anyone help me with the following? I am simply trying to add the three fields then divide by the last field. I have added a check for zero, where I want it to simply return Null if the field is zero and not perform the calculations. Any help will be greatly appreciated.`=(iif(Sum(Fields!TotalInvPrimaryPart.Value, "PartNumber")=0,Sum(Fields!TotalInv.Value, "PartNumber")+first(Fields!TotalInvPrim.Value, "PartNumber"),Sum(Fields!TotalInv.Value, "PartNumber"))+iif(Sum(Fields!QTYonOrderPrimaryPart.Value, "PartNumber")=0,Sum(Fields!TotalQTYonOrder.Value, "PartNumber")+first(Fields!QTYonOrderPrim.Value, "PartNumber"),Sum(Fields!TotalQTYonOrder.Value, "PartNumber"))+iif(Sum(Fields!QTYonPMRPrimaryPart.Value, "PartNumber")=0,Sum(Fields!TotalQTYonPMR.Value, "PartNumber")+first(Fields!QTYonPMRPrim.Value, "PartNumber"),Sum(Fields!TotalQTYonPMR.Value, "PartNumber")))/iif(Sum(Fields!AvgMonthDemand12PrimaryPart.Value, "PartNumber")=0,"Null",iif(Sum(Fields!AvgMonthDemand12PrimaryPart.Value, "PartNumber")>0,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")+first(Fields!AvgMonthDemand12Prim.Value, "PartNumber"),Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")`
Post #1468315
 Posted Friday, June 28, 2013 10:54 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, March 04, 2014 11:09 AM Points: 256, Visits: 587
 I changed up the expression to test for zero at the beginning. The expression looks good and does not reflect any errors, but I still get the #Error in the field when the report is executed. Any ideas?`=iif(iif(Sum(Fields!AvgMonthDemand12PrimaryPart.Value, "PartNumber")=0 ,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")+first(Fields!AvgMonthDemand12Prim.Value, "PartNumber") ,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber"))=0,"Null",( iif(Sum(Fields!TotalInvPrimaryPart.Value, "PartNumber")=0 ,Sum(Fields!TotalInv.Value, "PartNumber")+first(Fields!TotalInvPrim.Value, "PartNumber") ,Sum(Fields!TotalInv.Value, "PartNumber"))+ iif(Sum(Fields!QTYonOrderPrimaryPart.Value, "PartNumber")=0 ,Sum(Fields!TotalQTYonOrder.Value, "PartNumber")+first(Fields!QTYonOrderPrim.Value, "PartNumber") ,Sum(Fields!TotalQTYonOrder.Value, "PartNumber"))+ iif(Sum(Fields!QTYonPMRPrimaryPart.Value, "PartNumber")=0 ,Sum(Fields!TotalQTYonPMR.Value, "PartNumber")+first(Fields!QTYonPMRPrim.Value, "PartNumber") ,Sum(Fields!TotalQTYonPMR.Value, "PartNumber")))/ iif(Sum(Fields!AvgMonthDemand12PrimaryPart.Value, "PartNumber")=0 ,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")+first(Fields!AvgMonthDemand12Prim.Value, "PartNumber") ,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")))`
Post #1468630
 Posted Sunday, June 30, 2013 4:50 PM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 6:30 AM Points: 1,629, Visits: 5,115
 I can't see anything obviously wrong with the logic.What I would do next is put two textboxes on the report, one for`=( iif(Sum(Fields!TotalInvPrimaryPart.Value, "PartNumber")=0 ,Sum(Fields!TotalInv.Value, "PartNumber")+first(Fields!TotalInvPrim.Value, "PartNumber") ,Sum(Fields!TotalInv.Value, "PartNumber"))+ iif(Sum(Fields!QTYonOrderPrimaryPart.Value, "PartNumber")=0 ,Sum(Fields!TotalQTYonOrder.Value, "PartNumber")+first(Fields!QTYonOrderPrim.Value, "PartNumber") ,Sum(Fields!TotalQTYonOrder.Value, "PartNumber"))+ iif(Sum(Fields!QTYonPMRPrimaryPart.Value, "PartNumber")=0 ,Sum(Fields!TotalQTYonPMR.Value, "PartNumber")+first(Fields!QTYonPMRPrim.Value, "PartNumber") ,Sum(Fields!TotalQTYonPMR.Value, "PartNumber")))`and one for ` = iif(Sum(Fields!AvgMonthDemand12PrimaryPart.Value, "PartNumber")=0 ,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber")+first(Fields!AvgMonthDemand12Prim.Value, "PartNumber") ,Sum(Fields!TotalAvgMonthDemand12.Value, "PartNumber"))`Just to check they are returning the correct values.
Post #1468899
 Posted Monday, July 01, 2013 10:12 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, March 04, 2014 11:09 AM Points: 256, Visits: 587
 I placed the logic in two new textbox expressions and they both returned the correct data. I am stumped as to why I am still getting the #Error. Thanks for the reply.
Post #1469145
 Posted Monday, July 01, 2013 2:16 PM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, March 04, 2014 11:09 AM Points: 256, Visits: 587
 I got it to work using the following logic:`=IIF(a > 0, 200/IIF(a = 0, 1, a), 0)`Thanks for the help. Much appreciated.
Post #1469214
 Posted Tuesday, July 02, 2013 1:09 AM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 6:30 AM Points: 1,629, Visits: 5,115
 bpowers (7/1/2013)I got it to work using the following logic:`=IIF(a > 0, 200/IIF(a = 0, 1, a), 0)`Thanks for the help. Much appreciated.My next question was going to be if you had any tiny values for "a", but glad you found something that works. And thanks for sharing that - could be helpful for someone else later.
Post #1469319

 Permissions