ANS-0019 · SAVED SEARCHES & FORMULAS
How to Calculate Percentages of Summarized Columns in NetSuite Saved Searches?
Learn how to correctly compute percentage-based metrics from aggregated data within NetSuite saved searches using specific formula structures.
Short answer
To calculate percentages of summarized columns in NetSuite saved searches, use SUM(CASE WHEN ... THEN {Amount} ELSE 0 END) constructs for both the numerator and denominator. This approach ensures the aggregation happens before the division, providing accurate percentage results for summarized data, while also handling potential division by zero errors.
Scenario
Users often encounter challenges when attempting to calculate a percentage based on two summarized columns in a NetSuite saved search. While individual line-item percentages may appear correct, simply summing these percentages in a summarized view leads to inaccurate results. This typically occurs because the percentage calculation needs to happen on the aggregated totals, not on individual line items before aggregation.
Solution
To accurately calculate percentages of summarized columns, the aggregation must occur within the formula itself, before the division. This involves using SUM(CASE WHEN ... THEN {Amount} ELSE 0 END) for both the numerator and denominator. It is also crucial to incorporate NULLIF to prevent division by zero errors. Below are sample formulas demonstrating this approach: (SUM(CASE WHEN {account}'440050 Culling of Bins' THEN {Amount} ELSE 0 END)*-1)/nullif(SUM(CASE WHEN {accounttype}'Income' THEN {Amount} ELSE 0 END),0) SUM(CASE WHEN {account}'440050 Culling of Bins' THEN {Amount} ELSE 0 END) sum(case when {account} '611010 Overtime' then {Amount} else 0 end)/nullif(sum(CASE WHEN {account} '611000 Wages' THEN {Amount} ELSE 0 END),0) Round(SUM(NVL({quantityshiprecv},0)) / NULLIF(SUM(NVL({quantity},0)),0),2)
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support