ANS-0071 · SAVED SEARCHES & FORMULAS
How to Calculate Rolling 12-Month Sales in NetSuite Saved Searches
Leverage NetSuite saved search formulas to dynamically sum sales data over rolling periods, including current and previous year-to-date.
Short answer
To calculate rolling 12-month sales in NetSuite saved searches, use specific CASE WHEN formulas. These formulas evaluate transaction dates against a rolling 12-month window and sum the amount field. Separate formulas are provided for rolling current year-to-date and rolling previous year-to-date sales, ensuring accurate historical and current period analysis.
Scenario
NetSuite users often require dynamic reporting on sales performance over rolling periods, such as the last 12 months. This involves creating a saved search that can accurately aggregate sales data, distinguishing between current and previous rolling year-to-date figures. The challenge lies in constructing the correct formulas to define these rolling timeframes within the search criteria.
Solution
To calculate rolling 12-month sales in a NetSuite saved search, implement the following formulas. These formulas are designed to sum sales amounts based on specific date criteria, allowing for both rolling current year-to-date and rolling previous year-to-date calculations.Rolling This Year to Date Sales:CASE WHEN {custbody_bus_days_period_to_date} < {custbody_current_period.custrecord_bus_days_period_to_date} AND {trandate} ADD_MONTHS({today},-12)THEN {amount} ELSE 0 ENDRolling Previous Year to Date Sales:CASE WHEN {custbody_bus_days_period_to_date} < {custbody_current_period.custrecord_bus_days_period_to_date} AND {trandate} ADD_MONTHS({today},-24)THEN {amount} ELSE 0 END
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support