Menu
I am trying to evaluate a Price per Kilo ($/Kg) based on sales of a product. This works fine if the product was acutally sold during the period specified. However if the product is not sold the Kg (the denominator) ends up being 0 (zero) and an error results. - Divide by Zero error.
I tried this
=iif(KgSold=0,0,Revenue/KgSold)
It appears that the iif function is calculating both the true and false results. How do I get around this.
Should I be using the switch function instead?
=switch(KgSold=0,0
KgSold<>0,Revenue/KgSold)
825
Definition of SSRS IIF. IIF function is a decision function that returns one of two values based on whether or not a statement is true. If, Then, Else is a structure for an “immediate” IF function. Several IIFs could be nested in the very same way as Excel can. On a cell-by-cell basis, its function is assessed.
It is the trade group for global financial services industry. IIF was established in the year 1983 by 38 banks of leading industrialized countries. It was created in the aftermath of the international debt crisis of 1980s. The group now represent around 450 firms across 70 countries.
You're right, it doesn't short circuit. That sucks.
You'll have to do something like this:
= Iif(KgSold = 0, 0, Revenue) / Iif(KgSold = 0, 1, KgSold )
The switch function should also work.
187
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
