Hello, I am having issues in the calculation statement on a custom column because of #/DIV0! type errors. When using our INVENTORY database we have “QTY on hand” and “Committed QTY”. Than overall QTY on hand COGS. My custom column is to obtain the Committed QTY COGS. With some over committed QTY and returns we have values of “0” in the “QTY on hand” column and negative or >0 values in the “Committed QTY” column. Resulting in formula errors. Is there a “iferror” type statement I can use to correct these issues and not display them?
There’s a couple of ways you could accomplish this. If you have access to modify your database, you may want to consider implementing this as a new field using “If-Then” and adding it as a measure to your DB. I would think that would be the best way of doing it.
If that’s not an option, you can accomplish something similar in custom mode using a bit of an undocumented feature. You can use a SQL “case” statement in your calculated field.
You have to escape the SQL command with an “^” character, and enclose your variables in curly brackets “{ }”.
An example would be, if “a” is your committed qty, and “b” was your cost, you’d do something like this in your calculation:
^case when {a} = 0 then ‘0’ else {a} * {b} end
This would return a 0 value if the committed qty is 0, otherwise do the calculation (a * b).
2 Likes
Thank you for publishing this formula! It works great.
Thanks so much, this worked. Much appreciated.