use absolute value when dividing negative number

0
votes

I have a transaction saved search in which I have multiple formula columns to display quantities grouped by items when transaction types are Cash Sale and (Cash refund or Credit Memo). I'm now trying to add another formula column to divide returned quantities by sales quantities by using the below formula, but receiving an error "ERROR: Possible Divide by Zero" I believe the issue is that NetSuite referencing (-) negative returned quantities, and I need to figure out how to have it calculate this formula by using absolute value without the (-) negative. I've tried to sue the Saved Search absolute value function, but then the whole search errors out. Is this possible through case statement expression?

Formula: Case when {type} = 'Cash Refund' or {type} = 'Credit Memo' THEN {quantity} ELSE 0 END/(Case when {type} = 'Cash Sale' or {type} = 'Invoice' THEN {quantity} ELSE 0 END)

2
switch-statementnetsuitesaved-searches
Please share the formula used to divide returned quantities by sales quantities. - Sidd
For return_qty / sales_qty any period where there are returns without sales you will have a divide by 0. - Brian

2 Answers

1
votes

You can generally avoid divide by zero issues if you wrap the divisor in a NULLIF() function:

Case when {type} = 'Cash Refund' or {type} = 'Credit Memo' THEN {quantity} ELSE 0 END/NULLIF((Case when {type} = 'Cash Sale' or {type} = 'Invoice' THEN {quantity} ELSE 0 END), 0)
0
votes

Use ABS() to convert negative values to positive.

ABS(-4) returns 4.

Try this, ABS(return_qty) / ABS(sales_qty)

And use Nullif( , ) to avoid divide by ZERO error.