IIF fails on simple equation
CodeViper
Member Posts: 28
I have an IIF that looks like it is evaluating the full equation instead of acting on the first 'TRUE' part of the statement.
I have the following code in a cell on a report.
If their are NoTurns of Inventory, place a zero in the box. (Currently it shows ERR# when the value is zero).
And if there ARE NoTurns in Inventory, do the math calculation... which it does fine...
What am I missing??
This is the code I am using... I split it out to 4 lines just so I could visualize the layout/condition.
CODE START
=IIF( SUM(Fields!NoOfTurns.Value) = 0
, 0
, ((Sum(Fields!Item__Sales__Qty___.Value) + Sum(Fields!Item__Negative_Adjmt___Qty___.Value)) / Sum(Fields!AverageInventory.Value))
)
CODE END
I have tried using SWITCH, same issue. "=SWITCH(sum(Fields!NoOfTurns.Value) = 0, 0, sum(fields!NoOfTurns.Value > 0, *above equation*)
Shouldn't be rocket science to say "If your zero, print a zero"...
Any insight on how to fix this would be greatly appreciated... hate having a report show ERROR when I nice zero will do...
ADS
I have the following code in a cell on a report.
If their are NoTurns of Inventory, place a zero in the box. (Currently it shows ERR# when the value is zero).
And if there ARE NoTurns in Inventory, do the math calculation... which it does fine...
What am I missing??
This is the code I am using... I split it out to 4 lines just so I could visualize the layout/condition.
CODE START
=IIF( SUM(Fields!NoOfTurns.Value) = 0
, 0
, ((Sum(Fields!Item__Sales__Qty___.Value) + Sum(Fields!Item__Negative_Adjmt___Qty___.Value)) / Sum(Fields!AverageInventory.Value))
)
CODE END
I have tried using SWITCH, same issue. "=SWITCH(sum(Fields!NoOfTurns.Value) = 0, 0, sum(fields!NoOfTurns.Value > 0, *above equation*)
Shouldn't be rocket science to say "If your zero, print a zero"...
Any insight on how to fix this would be greatly appreciated... hate having a report show ERROR when I nice zero will do...
ADS
0
Best Answer
-
Hope this will help you:-
https://community.dynamics.com/nav/b/navisiontechnicalkulla/archive/2015/10/24/how-to-resolve-divide-by-zero-error-in-navision-rtc-report
Thanks for helping me to learn
1
Answers
-
Hi,
Please try with
=IIF(SUM(Fields!NoOfTurns.Value)=0,0,IIF(SUM(Fields!AverageInventory.Value)<>0,((SUM(Fields!Item__Sales__Qty___.Value) + SUM(Fields!Item__Negative_Adjmt___Qty___.Value)) /SUM(Fields!AverageInventory.Value)),0)). I checked it with some other variables and it worked fine.0 -

Its the same thing I get...
when there is inventory, I (and you) get a value... but when its zero... the ERR is present.
I put your formula in a new cell... 'high lighted in pic'
0 -
Hope this will help you:-
https://community.dynamics.com/nav/b/navisiontechnicalkulla/archive/2015/10/24/how-to-resolve-divide-by-zero-error-in-navision-rtc-report
Thanks for helping me to learn
1 -
well NavDeveloper... you saved my bacon...
who would have thought that RTC evaluates all the formulas first... then decides which to use...what a nightmare...
and THANK YOU... for helping me learn!!!!0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.7K Microsoft Dynamics NAV
- 18.8K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 329 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions