Quicksight跨年周数计算异常及Excel/Sheets对齐需求

你在Quicksight中使用以下公式计算周数：

dateDiff(truncDate('YYYY', {Date de Souscription}), {Date de Souscription}, 'WK')+1

该公式在2023年运行正常（周从周日开始，周数计算正确），但切换到2024年时出现异常：周数从1月1日开始计算，1月7日（周日）未作为新一周起始，周数规则随年份变化。需要调整为与Excel/Sheets一致的规则：周始终从周日开始，2024年第一周为1月1日至1月6日（仅6天）。

Quicksight中dateDiff使用WK单位时，周起始定义和年份第一周的判定逻辑并非完全匹配你的需求（它可能采用ISO周规则或其他年份周起始逻辑），导致跨年份时计算规则出现偏差。

使用以下自定义公式实现需求：

floor(
    (dateDiff(truncDate('YYYY', {Date de Souscription}), {Date de Souscription}, 'DD') + datePart('DW', truncDate('YYYY', {Date de Souscription})) - 1) / 7
) + 1

公式逻辑说明 #

1. truncDate('YYYY', {Date de Souscription})：获取当前日期所属年份的1月1日
2. dateDiff(..., 'DD')：计算当前日期与当年1月1日的天数差
3. datePart('DW', ...)：获取当年1月1日的星期数（Quicksight中DW返回1=周日，2=周一，…，7=周六）
4. 通过调整天数差与年初星期数的偏移量，确保每周从周日开始，且每年第一周从1月1日起至第一个周六结束，后续每周按周日到周六的周期计算周数

验证示例 #

• 2024年1月1日（周一）：计算得周数1
• 2024年1月6日（周六）：计算得周数1
• 2024年1月7日（周日）：计算得周数2
• 2023年1月1日（周日）：计算得周数1
• 2023年1月7日（周六）：计算得周数1
• 2023年1月8日（周日）：计算得周数2

内容的提问来源于stack exchange，提问作者And Row ID