如何在Spotfire中实现列值条件求和与自定义KPI计算？

原表达式的核心问题 #

1. 字段名混淆：你写的[kpi_value]实际对应表中的[KPIName]字段，导致CASE判断逻辑完全失效，无法正确匹配目标KPI类型。
2. 聚合逻辑嵌套错误：将外层SUM与窗口函数SUM嵌套使用，导致计算时重复聚合，结果偏离预期。
3. 窗口语法适配问题：如果使用的是类似Tableau的报表工具，Intersect([Station],[YearMth])不是标准的分区语法，应使用PARTITION BY来指定分组维度。

报表工具自定义表达式修正方案 #

如果直接在交叉表的值计算中自定义表达式，可针对不同KPIName返回对应结果：

CASE [KPIName]
    WHEN 'Total Units' THEN SUM([KPI Value])
    WHEN 'Total Hours' THEN SUM([KPI Value])
    WHEN 'Total Units/Hour' THEN 
        SUM(CASE WHEN [KPIName] = 'Total Units' THEN [KPI Value] ELSE 0 END) OVER (PARTITION BY [Station], [YearMth])
        / MAX(SUM(CASE WHEN [KPIName] = 'Total Hours' THEN [KPI Value] ELSE 0 END) OVER (PARTITION BY [Station], [YearMth]), 1)
END

• 对于Total Units和Total Hours，直接按Station+YearMth聚合求和；
• 对于Total Units/Hour，先通过窗口函数获取当前Station+YearMth维度下的两个总和，再做除法，用MAX(...,1)避免除以0的报错。

SQL预处理方案（更稳定） #

如果允许先通过SQL预处理数据再导入报表工具，逻辑会更清晰且不易出错：

WITH kpi_agg AS (
    SELECT
        Region,
        Station,
        YearMth,
        SUM(CASE WHEN KPIName = 'Total Units' THEN [KPI Value] ELSE 0 END) AS total_units,
        SUM(CASE WHEN KPIName = 'Total Hours' THEN [KPI Value] ELSE 0 END) AS total_hours,
        -- 用NULLIF处理除数为0的情况，结果为NULL而非报错
        SUM(CASE WHEN KPIName = 'Total Units' THEN [KPI Value] ELSE 0 END) / NULLIF(SUM(CASE WHEN KPIName = 'Total Hours' THEN [KPI Value] ELSE 0 END), 0) AS units_per_hour
    FROM your_table
    GROUP BY Region, Station, YearMth
)
-- 转成交叉表所需的行结构
SELECT Region, Station, YearMth, 'Total Units' AS KPIName, total_units AS [KPI Value] FROM kpi_agg
UNION ALL
SELECT Region, Station, YearMth, 'Total Hours' AS KPIName, total_hours AS [KPI Value] FROM kpi_agg
UNION ALL
SELECT Region, Station, YearMth, 'Total Units/Hour' AS KPIName, units_per_hour AS [KPI Value] FROM kpi_agg

预处理后的数据直接导入交叉表，无需再写复杂的自定义表达式，拖入对应字段即可生成结果。

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