NetSuite Saved Search Formula to Show Values by Week  

in , November 4th, 2024

A common ask I get from clients is a forecasting report showing their sales pipeline or task management. This is typically asked to be shown over the next few months. Saved Searches are great for this, and using the ADD_MONTH() makes it easy to take today’s month and add 1, 2, 3, etc. months to show the future months.

Recently, I was asked to show the sum of hours allocated for resources per week. Showing weekly amounts can be used with the TO_CHAR({datefield}, ‘WW’) function.

Step-by-Step Instructions

Step 1: Create a Saved Search.

Step 2: Add Summary fields that you also would like to show in your Results.

If you want to show Resource Allocation by Resource, you should Group your results by the Resource.

Step 3: Using the TO_CHAR({datefield}, ‘WW’), NetSuite will return the week number of the year. This allows you to use a CASE WHEN TO_CHAR({today}, ‘WW’) = TO_CHAR({datecreated}, ‘WW’) THEN ….

  • This function will show this week's amounts.
  • If you want to show next week's amounts, then you should have the following formula:

CASE WHEN TO_CHAR({today}+7, ‘WW’) = TO_CHAR({datecreated}, ‘WW’) THEN ….

-The ‘+7’ after the {today} field will take the current date plus seven days, giving you next week’s week number. If next week equals the week the Resource Allocation record was created, the amount will appear in that column. If you wanted to show two weeks out, you would have ‘{today}+14’ and so on.

Step 4: Your Results will then look like the following:

Author: Jack Mannebach


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!

 
 

Want to keep learning?

Our team of NetSuite professionals has written articles on a wide variety of NetSuite topics, from SuiteCommerce tips, to recommended NetSuite solutions, to available support services, and more! 

Your cart