ANS-0038 · SAVED SEARCHES & FORMULAS
How to Get the Last Amount by Date in a Grouped NetSuite Saved Search?
Learn to extract the most recent amount from grouped data in a NetSuite saved search using a specific formula field.
Short answer
To get the last amount by date in a grouped saved search, add a group option on the 'Type' field. Then, add a formula field to the Results list with a 'Maximum' summary type. Use the formula Max({amount}) KEEP(DENSE_RANK LAST ORDER BY {datecreated}) to retrieve the desired value.
Scenario
Users frequently encounter situations where they need to retrieve the most recent amount associated with a specific date within a grouped NetSuite saved search. Standard grouping functions may not directly provide the last value based on a date field, necessitating a specialized formula to accurately extract this information.
Solution
To obtain the last amount by date in a grouped saved search, begin by adding another group option on the Type field. Subsequently, add a formula field to the Results list. This new field must have the Maximum summary type selected. Finally, enter the following formula into the formula field: Max({amount}) KEEP(DENSE_RANK LAST ORDER BY {datecreated}).
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support