NetSuite已保存搜索公式使用：自定义KPI仪表盘搜索创建及公式报错求助

核心需求 #

已创建自定义列表字段KPI Account Group（值包含Expenses、Other Expenses、Interest Expense、Adjustments、Depreciation、Revenue、Other Income、Interest Earned、Fixed Asset），需基于该字段计算三个KPI：

• 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

原CASE公式因语法错误无法运行，以下是修复后的方案：

修复后的公式 #

Gross Burn 计算式 #

SUM(CASE WHEN {custrecord_kpi_account_group} = 'Expenses' THEN {balance} ELSE 0 END)
+ SUM(CASE WHEN {custrecord_kpi_account_group} = 'Other Expenses' THEN {balance} ELSE 0 END)
+ SUM(CASE WHEN {custrecord_kpi_account_group} = 'Interest Expense' THEN {balance} ELSE 0 END)
+ SUM(CASE WHEN {custrecord_kpi_account_group} = 'Adjustments' THEN {balance} ELSE 0 END)
+ (
  SUM(CASE WHEN {custrecord_kpi_account_group} = 'Fixed Asset' THEN {balance} ELSE 0 END)
  - SUM(CASE WHEN {custrecord_kpi_account_group} = 'Depreciation' THEN {balance} ELSE 0 END)
)

Net Burn 计算式 #

（可直接基于Gross Burn结果简化，或独立计算）

-- 基于已定义的Gross Burn字段简化写法
{custom_gross_burn}
- SUM(CASE WHEN {custrecord_kpi_account_group} = 'Revenue' THEN {balance} ELSE 0 END)
- SUM(CASE WHEN {custrecord_kpi_account_group} = 'Interest Earned' THEN {balance} ELSE 0 END)
- SUM(CASE WHEN {custrecord_kpi_account_group} = 'Other Income' THEN {balance} ELSE 0 END)

-- 独立计算写法
SUM(CASE WHEN {custrecord_kpi_account_group} = 'Expenses' THEN {balance} ELSE 0 END)
+ SUM(CASE WHEN {custrecord_kpi_account_group} = 'Other Expenses' THEN {balance} ELSE 0 END)
+ SUM(CASE WHEN {custrecord_kpi_account_group} = 'Interest Expense' THEN {balance} ELSE 0 END)
+ SUM(CASE WHEN {custrecord_kpi_account_group} = 'Adjustments' THEN {balance} ELSE 0 END)
+ (
  SUM(CASE WHEN {custrecord_kpi_account_group} = 'Fixed Asset' THEN {balance} ELSE 0 END)
  - SUM(CASE WHEN {custrecord_kpi_account_group} = 'Depreciation' THEN {balance} ELSE 0 END)
)
- SUM(CASE WHEN {custrecord_kpi_account_group} = 'Revenue' THEN {balance} ELSE 0 END)
- SUM(CASE WHEN {custrecord_kpi_account_group} = 'Interest Earned' THEN {balance} ELSE 0 END)
- SUM(CASE WHEN {custrecord_kpi_account_group} = 'Other Income' THEN {balance} ELSE 0 END)

EBITDA 计算式 #

（注：请将公式中的Revenue替换为实际对应"income"的账户分组值）

SUM(CASE WHEN {custrecord_kpi_account_group} = 'Depreciation' THEN {balance} ELSE 0 END)
+ SUM(CASE WHEN {custrecord_kpi_account_group} = 'Interest Expense' THEN {balance} ELSE 0 END)
+ SUM(CASE WHEN {custrecord_kpi_account_group} = 'Revenue' THEN {balance} ELSE 0 END)

原公式错误点说明 #

1. 使用了中文单引号‘’，需替换为英文单引号''
2. CASE语句结构混乱：未正确闭合END，且将运算符+/-写在THEN语句末尾，不符合语法规范
3. 未用SUM函数聚合同组账户的余额，单条CASE无法汇总所有符合条件的记录

内容的提问来源于stack exchange，提问作者Maira S