Quicksight中历史汇总与实时数据结合按月末计算员工总数方案咨询

原公式问题根源 #

你当前的公式对2021年11月之后的所有月份统一取now()对应的日期记录，导致历史月份（比如12月查看11月数据）无法匹配到上月月末的日期，只能拿到当前日期的匹配数据，逻辑不符合需求。

调整后逻辑 #

按数据时间分段适配统计规则：

• 2021年10月及之前的历史月度数据：本身只有月末全量记录，直接汇总所有HC类型数据即可
• 2021年11月及之后的日度数据：
  • 统计当前自然月的人数：取当日的HC记录求和
  • 统计已结束的历史月份人数：取该月最后一天的HC记录求和

调整后公式 #

（适配主流BI工具的函数语法，若使用Excel/其他工具可替换对应日期函数即可）

sumIf(
  {count_value},
  Type = 'HC'
  AND
  IF(
    # 2021年10月及以前，所有记录都参与统计
    EXTRACT('YYYY', ${Date}) < 2021 OR (EXTRACT('YYYY', ${Date}) = 2021 AND EXTRACT('MM', ${Date}) <= 10),
    TRUE,
    # 2021年11月及以后，匹配对应统计规则的日期
    ${Date} = IF(
      # 统计月份为当前正在进行的自然月，取今日日期
      EXTRACT('YYYY', ${Date}) = EXTRACT('YYYY', NOW()) AND EXTRACT('MM', ${Date}) = EXTRACT('MM', NOW()),
      DATE_TRUNC('day', NOW()),
      # 统计月份为已结束的历史月，取该月最后一天
      DATE_ADD(DATE_ADD(DATE_TRUNC('month', ${Date}), INTERVAL 1 MONTH), INTERVAL -1 DAY)
    )
  )
)

适配说明 #

• 公式没有硬编码后续年份规则，2022及之后的所有年份都可以自动适配，不需要每年调整
• 若你使用的工具不支持DATE_ADD/DATE_TRUNC函数，替换为对应工具的月末日期函数即可：比如Excel里用EOMONTH(${Date},0)取当月月末，逻辑完全一致
• 统一前置了Type='HC'的判断，减少重复逻辑，降低后续维护出错概率

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