NetSuite Saved Search Formulas – Using Date in Saved Search Formulas

NetSuite Saved Searches are a data query tool that can have many advanced search filters and results display options. Searches can be saved and run on-demand in NetSuite to extract the latest information. NetSuite saved search results provide reporting and data tracking capabilities that can be used to analyze any type of information in NetSuite and serve as the basis for business analysis and strategic decision-making.

Saved Search Formulas are a powerful feature of NetSuite Saved Searches that allow the users to query the database using Oracle SQL functions to filter or calculate results when creating your search.

Below is a list of date formula based on commonly requested reporting requirements by NetSuite users that can be used on your saved search filter or result column:

Date Format

• Year – TO_CHAR(date,’YYYY’)
• ISO Year – TO_CHAR(date,’IY’) or TO_CHAR(date,’IYYY’)
• Month – TO_CHAR(date,’M’)
• Name of Month – TO_CHAR(date,’MON’) or TO_CHAR(date,’MONTH’)
• Week of Year – TO_CHAR(date,’WW’)
• Week of Month – TO_CHAR(date,’W’)
• ISO Week – TO_CHAR(date,’IW’)
• Day of week – TO_CHAR(date,’D’)
• Name of Day – TO_CHAR(date,’DY’) or TO_CHAR(date,’DAY’)
• Day of Month – TO_CHAR(date,’DD’)
• Day of Year – TO_CHAR(date,’DDD’)

Queries

Results

Date Math

• Last Day of the Month – LAST_DAY(date)
• X Month from Current Date (Negative number will return past month) – ADD_MONTHS(date, X)
• Days Till Next Month – TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(date,1),’MONTH’),’MM/DD/YYYY’),’MM/DD/YYYY’) – date
• First Day of Next Month – TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(date,1),’MONTH’),’MM/DD/YYYY’),’MM/DD/YYYY’)

Queries

Results

Relative Year Queries

• Current Year– CASE WHEN date BETWEEN to_date(TRUNC({today}, ‘YEAR’), ‘MM/DD/YYYY’) AND to_date(last_day(add_months(trunc({sysdate},’YYYY’), 11))) THEN return_expr ELSE 0 END
• Year To Date– CASE WHEN date BETWEEN to_date(TRUNC({today}, ‘YEAR’), ‘MM/DD/YYYY’) AND to_date({today}) THEN return_expr ELSE 0 END
• Last Year– CASE WHEN date BETWEEN to_date(TRUNC(TRUNC({today}, ‘YEAR’)-1,’YEAR’), ‘MM/DD/YYYY’) AND TO_DATE(last_day(add_months(TRUNC(trunc({today},’YYYY’)-1,’YYYY’), 11))) THEN return_expr ELSE 0 END

Last Year To Date – CASE WHEN date BETWEEN to_date(TRUNC(TRUNC({today}, ‘YEAR’)-1,’YEAR’), ‘MM/DD/YYYY’) AND to_date(ADD_MONTHS({today},-12)) THEN return_expr ELSE 0 END

Query

Result

Result Using Date Filter

Relative Month Queries

• Current Month– CASE WHEN date BETWEEN to_date(TRUNC({today}, ‘MONTH’), ‘MM/DD/YYYY’) AND to_date(LAST_DAY{today}) THEN return_expr ELSE 0 END
• Month To Date– CASE WHEN date BETWEEN to_date(TRUNC({today}, ‘MONTH’), ‘MM/DD/YYYY’) AND to_date({today}) THEN return_expr ELSE 0 END
• Last Month– CASE WHEN date BETWEEN to_date(TRUNC(TRUNC({today}, ‘MONTH’)-1,’MONTH’), ‘MM/DD/YYYY’) AND to_date(LAST_DAY(TRUNC({today}, ‘MONTH’)-1)) THEN return_expr ELSE 0 END
• Last Month To Date– CASE WHEN date BETWEEN to_date(TRUNC(TRUNC({today}, ‘MONTH’)-1,’MONTH’), ‘MM/DD/YYYY’) AND to_date(ADD_MONTHS({today},-1)) THEN return_expr ELSE 0 END
• Next Month – CASE WHEN to_date(date,’MM/YYYY’) = TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS({today},1),’MONTH’),’MM/YYYY’),’MM/YYYY’) THEN return_expr ELSE 0 END
• Future Months – CASE WHEN date > last_day({today}) THEN return_expr ELSE 0 END

Query

Result

Result Using Date Filter

The above queries will return result at current time of the system. Here are some tips for creating Saved Searches in NetSuite that will help users weed out potential mistakes that can cause frustration and confusion with Saved Search formulas:

• Understand which data is being queried and which search type should be used for your Saved Search.
• Avoid entering contradicting data in the Criteria vs the Result tab. For example, if the saved search is filtering out only data from the current fiscal year, a query for last year data will return an error.
• Avoid using the wrong Formula type. When using formulas, if the user does not use the correct formula type, an “unexpected error” will occur. For example, if the Saved Search needs to return result in numeric value, using a text or date formula type will cause the Saved Search to error out.
• Review the query syntax. If the user does not follow the right syntax whether it is a missing comma or a typo, an “invalid expression” will occur. It is best to break down a complicated formula into smaller syntaxes when testing before combining them into a single query.
• Never use zero as the denominator in a formula. User should review the SQL queries before using any additional calculation to make sure the denominator is never zero. A NULLIFZERO () function can let the system know to exclude zero value, if any, from the calculation.
• Use “Main Line,” “Shipping Line” and “Tax Line” filter for your criteria. On a Transaction Saved Search, depending on if the user wants to see only header or line level information in the result, a Main Line filter need to be set to “True” (for Header information only) or “False” (for Line Level information only). Users also have the flexibility to see both at the same time but this might risk duplicating values depending on what needs to be pulled in the result view. Similar concept applies for tax and/or shipping lines. Users should set these filters depending on what information they want to see in the result.

Main Line = T

Main Line = F

• Be comfortable with trial and error. Patience is key when it comes to Saved Searches!

To learn more about how you can leverage NetSuite saved search formulas, contact our team today! 

RSM Staff