1

I have created a saved search in NetSuite which displays two separate case statements.

1- case when {transaction.type} = 'Item Receipt' and {transaction.account} = '5505 Cost of Goods Sold : Product Cost' or {transaction.type} = 'Item Fulfillment' and {transaction.account} = '5505 Cost of Goods Sold : Product Cost' then {transaction.amount} else null end

2- case when {transaction.type} = 'Invoice' or {transaction.type} = 'Cash Sale' or {transaction.type} = 'Cash Refund' or {transaction.type} = 'Credit Memo' then {transaction.amount} else null end

Now I need to add third column in which I can display % by dividing result values between case case statement 1 and 2. Any advise how is that possible? Thanks!

Imran
  • 93
  • 2
  • 15

1 Answers1

1

As Brian mentioned (and I presume you are doing) grouping is the easiest way.

NetSuite Search Example

You can use Minimum (as shown), Maximum, or Average for the Formula (Percent) field.

The IN statement shown above would also simplify your formulas.

Edit:

To avoid the Possible Divide by Zero error:

SUM(case when {transaction.account} = '5505 Cost of Goods Sold :Product Cost' and {transaction.type} in ('Item Fulfillment','Item Receipt') then {transaction.amount} else 0 end) / NULLIF( SUM(case when {transaction.type} in ('Invoice','Cash Sale','Cash Refund','Credit Memo') then {transaction.amount} else 0 end) ,0)

Nathan Sutherland
  • 1,191
  • 7
  • 9
  • Thanks! It's working great in all versions of my searches except just one with following error. Any idea how I can fix this? There is a divide by zero error in this search. It may be an error with a formula you have used. Please retry without the formula(s). If an error still occurs, please file with Customer Support. If it does not, please correct your formula. Typically that consists of taking the denominator and wrapping it in NULLIF(,0). – Imran Nov 05 '19 at 18:37
  • 1
    Correct, wrap the denominator SUM function in NULLIF – Nathan Sutherland Nov 05 '19 at 18:53
  • I’ve tried few different ways, but apparently they are not correct as I'm getting invalid expression errors. Sorry new to NS expressions. – Imran Nov 05 '19 at 21:25
  • I made an edit to my answer. Does that work for you? – Nathan Sutherland Nov 06 '19 at 16:39