Tableau：如何设置IF ELSE筛选列？员工高峰出勤判断计算字段求助

原代码问题点 #

1. 语法逻辑错误：直接将'17~19 peak'这类时段字符串当作字段名与数字比较，实际这些是Time字段的取值，并非独立字段
2. 缺少聚合逻辑：未针对员工维度做聚合统计，无法判断单个员工在不同时段的整体出勤情况

正确计算字段实现 #

方案一（使用WITH子句，Tableau 2020.2及以上版本支持） #

WITH 
peak_hour_worked = INT(MAX(IF [Time] = "17~19peak" THEN [workornot] ELSE 0 END)),
before_peak_worked = INT(MAX(IF [Time] = "15~17" THEN [workornot] ELSE 0 END)),
after_peak_worked = INT(MAX(IF [Time] = "19~21" THEN [workornot] ELSE 0 END))

IF peak_hour_worked = 1 AND before_peak_worked = 1 AND after_peak_worked = 1 THEN '全时段在岗'
ELSEIF peak_hour_worked = 1 AND before_peak_worked = 0 AND after_peak_worked = 0 THEN '仅高峰时段在岗'
ELSEIF peak_hour_worked = 0 AND before_peak_worked = 1 AND after_peak_worked = 0 THEN '高峰前时段在岗'
ELSEIF peak_hour_worked = 0 AND before_peak_worked = 0 AND after_peak_worked = 1 THEN '高峰后时段在岗'
ELSE '其他出勤类型'
END

方案二（兼容所有Tableau版本） #

IF 
MAX(IF [Time] = "17~19peak" THEN [workornot] ELSE 0 END) = 1 
AND MAX(IF [Time] = "15~17" THEN [workornot] ELSE 0 END) = 1 
AND MAX(IF [Time] = "19~21" THEN [workornot] ELSE 0 END) = 1 
THEN '全时段在岗'

ELSEIF 
MAX(IF [Time] = "17~19peak" THEN [workornot] ELSE 0 END) = 1 
AND MAX(IF [Time] = "15~17" THEN [workornot] ELSE 0 END) = 0 
AND MAX(IF [Time] = "19~21" THEN [workornot] ELSE 0 END) = 0 
THEN '仅高峰时段在岗'

ELSEIF 
MAX(IF [Time] = "17~19peak" THEN [workornot] ELSE 0 END) = 0 
AND MAX(IF [Time] = "15~17" THEN [workornot] ELSE 0 END) = 1 
AND MAX(IF [Time] = "19~21" THEN [workornot] ELSE 0 END) = 0 
THEN '高峰前时段在岗'

ELSEIF 
MAX(IF [Time] = "17~19peak" THEN [workornot] ELSE 0 END) = 0 
AND MAX(IF [Time] = "15~17" THEN [workornot] ELSE 0 END) = 0 
AND MAX(IF [Time] = "19~21" THEN [workornot] ELSE 0 END) = 1 
THEN '高峰后时段在岗'

ELSE '其他出勤类型'
END

注意事项 #

• 确保视图中已添加员工维度（如员工ID/姓名），聚合计算需基于员工个体生效
• MAX()函数的作用：判断该员工在对应时段是否存在至少一次在岗记录（只要有一条workornot=1，MAX结果即为1）
• 若需要严格判断“该时段全程在岗”而非“至少一次在岗”，可将MAX()替换为MIN()（需确保该时段所有记录的workornot均为1）

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