How to use formula in netsuite saved search?

Hi,
I want to create Custom KPI saved search for Dashboard view. Custom field ‘KPI Account Group’ created to classify accounts into groups for KPI formulas Custom List: KPI Account Group Values: Expenses, Other Expenses, Interest Expense, Adjustments, Depreciation, Revenue, Other Income, Interest Earned, Fixed Asset

Now I want to calculate following in saved search and show in custom KPI

Custom KPI Formulas–> Gross Burn: Expenses + Other Expenses + Interest Expense + Adjustments + (Fixed Assets – Depreciation)

Net Burn: Gross Burn – Revenue – Interest Earned – Other Income

EBITDA: Depreciation + Interest Expense + income

Please help! Thanks in advance.

I have created saved search on account record. criteria: KPI Account Group is any of Values: Expenses, Other Expenses, Interest Expense, Adjustments, Depreciation, Revenue, Other Income, Interest Earned, Fixed asset. Result: Balance

Beginner Asked on December 6, 2023 in Saved Searches.
Add Comment
5 Answer(s)

How to create custom formula for this?

  • Gross Burn: Expenses + Other Expenses + Interest Expense + Adjustments + (Fixed Assets – Depreciation)
  • Net Burn: Gross Burn – Revenue – Interest Earned – Other Income
  • EBITDA: Depreciation + Interest Expense + Revenue
Beginner Answered on December 6, 2023.
Add Comment

{expenses} + {other expenses} + {interest expense} + {adjustments} + ({fixed assets} – {depreciation})

these are not field internal ids. These are custom list values of KPI Account Group custom field. I have to use that to classify the account as

Expenses, Other Expenses, Interest Expense, Adjustments, Depreciation, Revenue, Other Income, Interest Earned, Fixed Asset

Beginner Answered on December 6, 2023.
Add Comment

I am using formula in saved search like this but its giving invalid expression error

CASE WHEN {custrecord1} = ‘Expenses’ THEN {balance}+
CASE WHEN {custrecord1} = ‘Other Expenses’ THEN {balance}+
CASE WHEN {custrecord1} = ‘Interest Expense’ THEN {balance}+
CASE WHEN {custrecord1} = ‘Adjustments’ THEN {balance}+
CASE WHEN {custrecord1} = ‘Depreciation’ THEN {balance}+
CASE WHEN {custrecord1} = ‘Revenue’ THEN {balance}+
CASE WHEN {custrecord1} = ‘Other Income’ THEN {balance}+
(CASE WHEN {custrecord1} = ‘Interest Earned’ THEN {balance}-
CASE WHEN {custrecord1} = ‘Fixed Asset’ THEN {balance}) ELSE 0
END

Beginner Answered on December 6, 2023.
Add Comment

Hi Maira,

Sure, I can help you create the Custom KPI saved search for Dashboard view.

Here are the steps:

  1. Create a custom KPI formula for each of the following:
    • Gross Burn: Expenses + Other Expenses + Interest Expense + Adjustments + (Fixed Assets – Depreciation)
    • Net Burn: Gross Burn – Revenue – Interest Earned – Other Income
    • EBITDA: Depreciation + Interest Expense + Revenue
  2. Create a saved search that includes the following criteria:
    • KPI Account Group is any of Values: Expenses, Other Expenses, Interest Expense, Adjustments, Depreciation, Revenue, Other Income, Interest Earned, Fixed Asset
    • Result: Balance
  3. Add the custom KPI formulas to the saved search.
  4. Save the saved search and add it to your dashboard view.

Here is an example of how to create the custom KPI formula for Gross Burn:

Gross Burn: Expenses + Other Expenses + Interest Expense + Adjustments + (Fixed Assets – Depreciation)

This formula will calculate the sum of the Expenses, Other Expenses, Interest Expense, and Adjustments fields, and then add the difference between the Fixed Assets and Depreciation fields.

Here is an example of how to create the saved search:

Name: Custom KPI Saved Search Criteria: * KPI Account Group is any of Values: Expenses, Other Expenses, Interest Expense, Adjustments, Depreciation, Revenue, Other Income, Interest Earned, Fixed Asset * Result: Balance

This saved search will return all of the account records that have a KPI Account Group of Expenses, Other Expenses, Interest Expense, Adjustments, Depreciation, Revenue, Other Income, Interest Earned, or Fixed Asset. The saved search will also return the Balance field for each account record.

Here is an example of how to add the custom KPI formulas to the saved search:

Custom KPI Formulas: * Gross Burn: Expenses + Other Expenses + Interest Expense + Adjustments + (Fixed Assets – Depreciation) * Net Burn: Gross Burn – Revenue – Interest Earned – Other Income * EBITDA: Depreciation + Interest Expense + Revenue

This will add the custom KPI formulas to the saved search. The saved search will now calculate the Gross Burn, Net Burn, and EBITDA for each account record.

Here is an example of how to save the saved search and add it to your dashboard view:

  1. Click the Save button.
  2. Enter a name for the saved search, such as “Custom KPI Saved Search”.
  3. Click the Save button.
  4. Click the Add to Dashboard button.
  5. Select the dashboard view that you want to add the saved search to.
  6. Click the Add button.

The saved search will now be added to your dashboard view. The saved search will calculate the Gross Burn, Net Burn, and EBITDA for each account record and display the results in the dashboard view.

Beginner Answered on December 6, 2023.
Add Comment

You can create a custom formula for this in NetSuite by following these steps:

Gross Burn Formula:

  1. Navigate to the custom record where you want to create the formula field.

  2. Open the “Validation & Defaulting” subtab.

  3. Check the “Formula” box.

  4. In the “Formula” field, enter the following formula:

{expenses} + {other expenses} + {interest expense} + {adjustments} + ({fixed assets} – {depreciation})

Net Burn Formula:

  1. Create another formula field and enter the following formula:
{gross burn} - {revenue} - {interest earned} - {other income}

EBITDA Formula:

  1. Create another formula field and enter the following formula:
{depreciation} + {interest expense} + {revenue}

Remember to replace the placeholder names with the actual field names from your NetSuite records.

Beginner Answered on December 6, 2023.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Become a Sponsor   Become a Sponsor