nullif when zero or negative value

Question

I frequently use the nullif when value equals to zero, and wondering if it's possible to also display null when it equals to negative number? My current formula is NULLIF({quantity}-{quantityshiprecv} ,0) but this displays negative numbers when {quantityshiprecv} is greater than {quantity}, and I'm hoping to display null.

Answer 1

You just need to modify the first argument to return 0 if {quantity}-{quantityshiprecv} is negative, but still give the correct answer if it's positve. Here's one way:

NULLIF(({quantity}-{quantityshiprecv} + ABS({quantity}-{quantityshiprecv}))/2,0) 

This adds the absolute value of the difference to the unchanged difference. If the difference is positive, that gives you double what you want. If it's negative the absolute (ABS()) will negate that negative value leaving you with zero. You can then divide by 2; positive is now correct, negative remains zero. The rest of the NULLIF() remains the same.