Rounding Summarized Saved Search Results

0
votes

I created a saved search on sales orders to calculate days between order date and ship date, grouped and averaged by class (the formula is cased to distinguish between 'Wholesale' class and all others). The numeric formula is:

CASE WHEN {class} = 'Wholesale' THEN {actualshipdate} - {startdate} ELSE {actualshipdate} - {shipdate} END

The summary type for the formula result is average. The summary-level results have way too many decimal places. Is there a way to round the summarized results to a pre-defined number of digits?

I have tried using the ROUND() function, both inside the CASE statement and as a wrap-around. I've also looked through general preferences for rounding defaults and haven't found any.

1
netsuite

1 Answers

0
votes

In order to round the result, you should implement you own average calculation and rounding.

Add the following result field besides the class grouping field that you already have:

Field : Formula (Numeric)
Summary Type : Maximum
Formula : ROUND(SUM(CASE WHEN {class} = 'Wholesale' THEN {actualshipdate} - {startdate} ELSE {actualshipdate} - {shipdate} END) / COUNT({internalid})),2)

Basically, you are doing here your own analytics. Since the results are grouped, each result fields contains the whole result set of this field which allows you to add analytical functions on this group. The summary type is MAXIMUM, just in order to show one result, so it can also be min or avg, no difference. The function is calculating the sum of your formula and dividing it by the records count to get the average.