Here's a sample Transaction Saved Search with a formula to compare the prior month's Invoices amount to the current month's Invoices amount per Customer.
- Navigate to Lists > Search > Saved Searches > New
- Select Transaction
- Rename the Saved Search
- Under the Criteria tab, mark Use Expressions checkbox
- Still under the Criteria tab, add the following filters:
Formula 1 = CASE WHEN {trandate} BETWEEN TRUNC({today},'MM') and LAST_DAY({today}) THEN {amount} ELSE 0 END
Formula 2 = CASE WHEN {trandate} BETWEEN TRUNC(ADD_MONTHS({today},-1),'MM') and LAST_DAY(ADD_MONTHS({today},-1)) THEN {amount} ELSE 0 END
6. Under the Results tab, add the following fields:
Formula 1 = CASE WHEN {trandate} BETWEEN TRUNC({today},'MM') and LAST_DAY({today}) THEN {amount} ELSE 0 END
Formula 2 = CASE WHEN {trandate} BETWEEN TRUNC(ADD_MONTHS({today},-1),'MM') and LAST_DAY(ADD_MONTHS({today},-1)) THEN {amount} ELSE 0 END
Note: Add other fields as necessary.
7. Click Save & Run
I hope this helps! ❤ Feel free to share your own tips and tricks under NetSuite Admin Corner to help other members as well. Thank you very much! 😊