Calculate the average amounts for transactions of a month

In NetSuite’s SuiteScript or Saved Searches, you can use the following formula to calculate the average of negative amounts for transactions that occurred in March:

SUM(CASEWHEN TO_CHAR({trandate}, ‘MM’) =’03’THEN–NVL({amount}, 0) ELSE0END) /6

Steps to Implement in NetSuite:

1. Create or Edit a Saved Search:

• Navigate to Reports > Saved Searches > All Saved Searches > New (or edit an existing one).

1. Choose Record Type: Select the appropriate record type (e.g., Transactions).
2. Add Formula Field:

• Go to the Results tab.
• Add a new field.
• Set the field type to Formula and select Numeric.
• In the formula field, paste the SQL code above.

1. Ensure Grouping (if needed):

• If you want an overall average rather than for each group, ensure that you are not grouping by any other fields unless intended.

1. Run the Search: Save and run your search to see the results.