A client needed a NetSuite saved search that displays all payment transactions month-to-day, with a column specifically for a daily running total (a row-by-row sum of each day’s respective payment transactions, reset for the next day).
Adding a Running Total to Our Client's NetSuite Saved Search
To have a running daily total result, you must create a formula using a few specific functions, those being SUM(), PARTITION BY(), and ORDER BY(), and using the payment dates and amounts. The specific formula for this example is as follows, but is, of course, customizable to varying situations.
SUM /* Daily Running Total*/ ({amount}) OVER (PARTITION BY TO_DATE({trandate}) ORDER BY {number} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Example of a NetSuite Saved Search Running Total
The screenshot below outlines what this formula is doing. Looking at the date and payment amounts of the first day (blue), one can see that the last row of 12/1 payment transactions on the Daily Running Total column (yellow) is the sum of all the 12/1 payments, but resets on 12/2 (green) and continues a new daily running total for 12/2 (red).
Author: Grant Steele
Got stuck on a step in this article?
We like to update our blogs and articles to make sure they help resolve any troubleshooting difficulties you are having. Sometimes, there is a related feature to enable or a field to fill out that we miss during the instructions. If this article didn't resolve the issue, please use the chat and let us know so that we can update this article!
Oracle NetSuite Alliance Partner & Commerce Partner
If you have general questions about SuiteCommerce or more specific questions about how our team can support your business as you implement NetSuite or SuiteCommerce, feel free to contact us anytime. Anchor Group is a certified Oracle NetSuite Alliance Partner and Commerce Partner equipped to handle all kinds of NetSuite and SuiteCommerce projects, large or small!
