如何修改Tableau公式，按条件调整指定日期活动的Credits Used MAX值

需求规则 #

• 当同一员工同一日期存在多条活动记录时，将满足以下条件的记录的「Credits Used MAX」值设为0：
  • activity_category = TimeOff
  • activity_name = LOA UTO
  • activity_duration = 30
• 若该员工当天仅有上述这一条活动记录，则保持「Credits Used MAX」值为1。

数据示例 #

多记录场景（需修改目标值） #

Employee        Date        activity_category   activity_name   activity_duration   Credits Used MAX
Gracie        7/15/2024             TimeOff           LOA UTO             30                       1
Gracie        7/16/2024             TimeOff           LOA UTO             30                       1
Gracie        7/23/2024               Break           Break               15                       0
Gracie        7/23/2024               Meal            Meal                30                       0 
Gracie        7/23/2024              TimeOff          LOA UTO             30                       1  -- 需改为0
Gracie        7/23/2024              On Queue         On Queue            120                      0

单条记录场景（保持原值） #

Employee             Date       activity_category   activity_name   activity_duration   Credits Used MAX
Gracie             7/23/2024      TimeOff             LOA UTO              30                   1  -- 保持1

现有公式（中文翻译） #

IF [activity_category] = 'OnQueueWork' 
  AND [activity_name (Custom SQL Query2)] IN ('OnQueueWork', 'On Queue') 
  AND INT([activity_duration]) >= 120 
THEN 0 
ELSEIF [activity_category] = 'TimeOff' 
  AND INT([activity_duration]) >= 120 
THEN 1 
ELSEIF { FIXED [Date] : COUNTD([Date]) } = 1 
THEN 
  IF [activity_category] = 'TimeOff' 
    AND [activity_name (Custom SQL Query2)] = 'LOA UTO' 
    AND INT([activity_duration]) = 30 
  THEN 1 
  ELSEIF [activity_category] = 'OnQueueWork' 
    AND [activity_name (Custom SQL Query2)] = 'Overtime' 
    AND INT([activity_duration]) = 30 
  THEN 1 
  ELSE 0 
  END 
ELSEIF { FIXED [Date], [activity_category], [activity_name (Custom SQL Query2)], [activity_duration] : COUNT([Date]) } > 1 
THEN 
  IF [activity_category] = 'TimeOff' 
    AND [activity_name (Custom SQL Query2)] = 'LOA UTO' 
    AND INT([activity_duration]) <= 30 
  THEN 0 
  ELSE 0 
  END 
ELSE 0 
END

调整后的公式 #

IF 
  -- 定位目标LOA UTO记录
  [activity_category] = 'TimeOff' 
  AND [activity_name (Custom SQL Query2)] = 'LOA UTO' 
  AND INT([activity_duration]) = 30 
THEN 
  -- 判断该员工当天总记录数
  IF { FIXED [Employee], [Date] : COUNT(*) } = 1 THEN 1 ELSE 0 END 
-- 保留原有其他业务逻辑
ELSEIF [activity_category] = 'OnQueueWork' 
  AND [activity_name (Custom SQL Query2)] IN ('OnQueueWork', 'On Queue') 
  AND INT([activity_duration]) >= 120 
THEN 0 
ELSEIF [activity_category] = 'TimeOff' 
  AND INT([activity_duration]) >= 120 
THEN 1 
ELSEIF [activity_category] = 'OnQueueWork' 
  AND [activity_name (Custom SQL Query2)] = 'Overtime' 
  AND INT([activity_duration]) = 30 
THEN 1 
ELSE 0 
END

公式说明 #

1. 核心逻辑优化：用{ FIXED [Employee], [Date] : COUNT(*) }准确统计同一员工同一天的总记录数，替代原公式中仅按Date统计的错误逻辑。
2. 优先级调整：把目标LOA UTO的判断放在最前面，确保规则优先触发。
3. 简化冗余代码：移除原公式中重复的ELSE 0分支，让逻辑更简洁易读。
4. 兼容原有规则：完全保留原公式中关于OnQueueWork长时长、TimeOff长时长、Overtime的判断逻辑，不影响其他场景的计算。

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