7 Replies Latest reply on Sep 6, 2020 11:31 PM by Joseph_

# How do I divide the summary's evaluated value in formula instead of it's row?

Hello, I am creating a Saved Search using formulas to divide two Transaction Body fields containing a formula.

Let's call this Field A:

```ABS(CASE WHEN {quantity}<0  THEN {quantity} ELSE 0 END)
```

Let's call this Field B:

```CASE WHEN {quantity}>0  THEN {quantity} ELSE 0 END
```

Here is what I am attempting to do with these two formulas:

```Field A / Field B
```

This is the summary page of my item saved search:

As you may have figured from the given two formulas, I would receive an error because when I click the Inventory Item, the transactions that make up this number is always divided by zero. The zero is entered from the ELSE statement. Field A is a positive quantity integer of sold items. Field B is a negative quantity integer turned into a positive number to indicate items returned.

Do you know a workaround to perform the divide without having the system evalulate the components?

• ###### 1. Re: How do I divide the summary's evaluated value in formula instead of it's row?

Hey Joseph,

I am not sure to understant what you are trying to accomplish here, but did you try re-write the fields formula in one formula within the saved search?

• ###### 2. Re: How do I divide the summary's evaluated value in formula instead of it's row?

Hello Nairolf,

I am attempting to calculate the return rate of products sold on a per item basis.

Field A = Quantity Returned

Field B = Quantity Sold

In my head, to obtain a return rate, you will need to divide Field A with Field B.

For example, I sold 8 apples, and 4 of them returned to me:

4 out of 8 = 4/8 = 1/2 = 0.5 = 50% return rate

With how quantities are handled in NetSuite Transaction Saved Searches, Return Authorisations mark quantity as negative numbers.

My work around to it was to mark all negative quantity as a number towards returns.

Since I am working with Transactions summarised as a per item basis, I need to group and sum formula fields.

Because Return Authorisations and Sales Orders/Invoices/ and other transactions denote a positive number, there will be zeroes on the field A and B formulas.

If I can apply the formula on the summarised numbers only, I can obtain the goal of a return rate formula.

This will help employees make an important decision as to what items needs to be pulled from the shelves so customers will not have to eat rotten apples.

• ###### 3. Re: How do I divide the summary's evaluated value in formula instead of it's row?

Hello Joseph,

Did you try to do your saved search on the item instead of on the transactions? I'm not in front of my computer, but I would see something like case when transaction type is RMA then abs(qty) when type is SO then qty...

Please try and tell me if that would work. If not I'll try on my hand.

Good luck!

• ###### 4. Re: How do I divide the summary's evaluated value in formula instead of it's row?

Hello Nairolf,

I have attempted to recreate the saved search under Items instead.

When I attempt to divide Quantity Returned with Quantity Sold formula fields again, the result is an empty column.

Is there another way than to use this as the formula for the return rate formula column?

(CASE WHEN {transaction.type}='Return Authorisation' THEN {transaction.quantity} END) / (CASE WHEN {transaction.type}='Invoice' THEN {transaction.quantity} END)

(Field A) / (Field B)

When I clicked on an item to see how the division is happening, there is always a field that is blank so the division is not taking place on the summarised values.

• ###### 7. Re: How do I divide the summary's evaluated value in formula instead of it's row?

I did not know that the numerator and the denominator needed an additional SUM() function to wrap each value before proceeding to divide them.

Thank you so much for your help.