How to use formula in netsuite saved search

Question

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 am using formula in saved search like this but its giving invalid expression error CASE WHEN {custrecord1} = ‘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

Answer 1

This is not quite the right format for a CASE statement. It's hard to know exactly what formula you need without seeing the rest of your Search, but it looks like several custrecord1 values should map to positive balance while Interest Earned should map to negative balance.

A CASE statement for that looks more like:

CASE {custrecord1}
  WHEN 'Expenses' THEN {balance}
  WHEN 'Interest Expense' THEN {balance}
  WHEN 'Adjustments' THEN {balance}
  WHEN 'Depreciation' THEN {balance}
  WHEN 'Revenue' THEN {balance}
  WHEN 'Other Income' THEN {balance}
  WHEN 'Fixed Asset' THEN {balance}
  WHEN 'Interest Earned' THEN -1 * {balance}
ELSE 0
END