如何在Amazon QuickSight中创建适配用户日期筛选的MoM KPI

问题描述 #

我正尝试在Amazon QuickSight（QS）中创建一个展示月度环比（Month Over Month，MoM）表现的KPI，使其适配用户筛选的月份，思路是让日期筛选不作用于该KPI。

我的尝试步骤：

1. 创建计算字段获取最大日期：

   maxIf({data_ref}, {data_ref} >= addDateTime(0, 'YYYY', truncDate('MM', now())) AND  {data_ref} <= addDateTime(0, 'DD', addDateTime(-3, 'HH', now())) )
   

2. 在另一个计算字段中使用该最大日期：

   countIf({num_pedi_mrke}, truncDate("MM", {data_ref}) = truncDate("MM", {Max_date})) /  countIf({num_pedi_mrke}, truncDate("MM", {data_ref}) = truncDate("MM", addDateTime(-1, 'MM', {Max_date})))
   

执行后出现错误：

不允许使用{{aggregateFunction1}}和{{aggregateFunction2}}这类嵌套聚合函数。

请问如何创建适配用户筛选月份的MoM KPI并避免该错误？是否有其他方案？

解决方案 #

要解决嵌套聚合的限制问题，可以通过以下两种方案实现需求：

方案一：使用参数+级别计算（推荐） #

QuickSight的参数可以灵活关联筛选逻辑，同时级别计算能避开嵌套聚合的限制：

1. 创建日期参数：新建一个名为selected_month的日期参数，设置为月份粒度，关联到你的日期筛选器，让用户可以选择目标月份。
2. 创建当前月数值计算字段：

   countIf({num_pedi_mrke}, truncDate('MM', {data_ref}) = truncDate('MM', ${selected_month}))
   

3. 创建上月数值计算字段：

   countIf({num_pedi_mrke}, truncDate('MM', {data_ref}) = truncDate('MM', addDateTime(-1, 'MM', ${selected_month})))
   

4. 计算环比值：

   ${当前月数值} / ${上月数值} - 1
   

   （后续可在KPI可视化设置中，将格式调整为百分比显示）
5. 排除筛选器对KPI的直接影响：编辑KPI组件时，在「筛选器」选项卡中移除日期筛选器的应用，仅保留参数关联逻辑。

方案二：自定义SQL预聚合数据 #

在数据源层面提前完成月度聚合，避免在QuickSight中做嵌套计算：

1. 修改数据源SQL，按月份预聚合数据：

   SELECT
       DATE_TRUNC('month', data_ref) AS month_ref,
       COUNT(num_pedi_mrke) AS total_pedi
   FROM your_table_name
   GROUP BY DATE_TRUNC('month', data_ref)
   

2. 创建上月数值计算字段：使用lookup函数匹配上月数据

   lookup(total_pedi, month_ref, addDateTime(-1, 'MM', month_ref))
   

3. 计算环比值：

   total_pedi / lookup(total_pedi, month_ref, addDateTime(-1, 'MM', month_ref)) - 1
   

4. 同样通过日期参数关联筛选器，让用户选择月份后自动匹配对应环比。

关键注意事项 #

• 严格避免在聚合函数（如countIf）中嵌套另一个聚合函数（如maxIf），这是QuickSight的核心语法限制；
• 使用参数替代直接引用筛选器值，既能灵活控制计算逻辑，又能避开筛选器对聚合结果的直接干扰；
• 若数据集较大，预聚合SQL方案的查询性能会优于级别计算方案。

内容的提问来源于stack exchange，提问作者Lucas Dall'Aqua Di Fonzo