Performing calculation of two separate case statements

Question

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!

Answer 1

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

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)