Spotfire：使用Color by时Average Over LastPeriods函数异常问题

我要做堆叠条形图展示各分类过去6个月的平均支出，数据包含Month、Category、Amount三个字段。当前图表配置：

• 分类轴：[Month]
• 颜色按[Category]区分
• 值轴表达式：Avg([Amount]) OVER (LastPeriods(6,[Axis.X]))

但只要某个月份缺了某类支出的记录（比如April没Holiday支出），对应的条形段就不显示，平均值还会返回空值。用交叉表验证时，缺失组合的单元格也显示--，根本算不出想要的滚动平均值。要是给缺失的Month-Category组合补Amount=0的记录，现有100万条、50多列的数据量会直接涨20倍，完全不可行。

下面是几个可行的解决办法：

1. 用COALESCEEMPTY填充空值后计算平均值 #

直接修改值轴的表达式，把空的Amount自动换成0再算平均：

Avg(COALESCEEMPTY([Amount], 0)) OVER (LastPeriods(6,[Axis.X]))

这个办法不用改原始数据，计算时会自动处理缺失的记录，保证滚动6个月的平均值能正常出结果。

2. 创建计算成员补全维度成员 #

如果上面的方法不管用，可以在Category维度里加计算成员，确保每个月份都包含所有分类：

• 给每个可能缺失的Category-Month组合创建计算成员，没有记录就返回0
• 然后修改平均值计算的表达式，基于完整的成员集来算：

Avg(
  LastPeriods(6, [Axis.X]),
  COALESCEEMPTY([Amount], 0)
)

这样会强制遍历过去6个月的所有Month-Category组合，哪怕原始数据里没有，也会用0填充后计算平均。

3. 在查询层生成完整的时间-分类笛卡尔积 #

如果数据源是SQL，可以在查询的时候生成Month和Category的全组合，再左连接原始数据补0，之后再导入可视化工具：

WITH all_month_categories AS (
  SELECT DISTINCT m.Month, c.Category
  FROM (SELECT DISTINCT Month FROM your_table) m
  CROSS JOIN (SELECT DISTINCT Category FROM your_table) c
)
SELECT 
  amc.Month,
  amc.Category,
  COALESCE(t.Amount, 0) AS Amount
FROM all_month_categories amc
LEFT JOIN your_table t 
  ON amc.Month = t.Month AND amc.Category = t.Category

这种方式是在查询时临时生成完整数据集，不用改原始表，也不会导致数据量永久暴增，完美避开补0的副作用。

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