CODEWITHSUNDEEP
formulanetsuite
Marcel P
Marcel P

Reputation: 65

NetSuite - Formula in a Saved Search to calculate a percentage of approved Sales Orders

I would like to create a Saved Search in NetSuite that returns the percentage of Sales Orders that have been approved in a specific period, however I haven’t been able so far to find out a formula that returns this information.

I was able to add a result field with the count of Sales Orders and a formula field that returns the count of approved Sales Orders, (ie. formula (SUM): CASE WHEN {status} != 'Pending Approval' THEN 1 ELSE 0 END), but I would like to calculate the percentage of approved Sales Orders only using a Saved Search.

A possible solution that I can see is to create the Saved Search with those two fields and a Script (Portlet / Suitelet) to run the Saved Search, calculate the value and display to the user. Does anyone know an easier way to achieve it, if possible, only using the Saved Search?

Thank you.

Upvotes: 0

Views: 8759

Answers (2)

bknights
bknights

Reputation: 15402

Actually this is possible. In a nutshell enter:

sum(case when {status} = 'Approved' then 1 else 0 end) / count({tranid})

as a Formula (Percent) column on a saved search and apply an aggregate function to it (Average, Maximum or Minimum all produce the same result)

see: Formula in Netsuite Saved Search

Upvotes: 4

prasun
prasun

Reputation: 7343

to complete the answer,

The best possible solution is you can get a count of approved and total SO and take the percentage in Suitelet/Restlet/Portlet script.

Getting a percentage would require to write a formula based on other search columns which isn't possible in NS

Upvotes: 0

Related Questions