Cognos Analytics（Costpoint 8.0.0）服务年限周年判定公式求助

需要在Cognos Analytics（Costpoint 8.0.0）中编写公式，判断员工当月是否达到服务年限里程碑（5、10、15、20、25、30年），返回“是/否”或布尔值（真/假）即可。服务年限对应Costpoint中的[Years of Service]字段，为数字格式（如9.154、4.982）。

已实现的Excel参考公式：

=IF(OR(AND(A1>4.91,A1<=5),(AND(A1>9.91,A1<=10)),(AND(A1>14.91,A1<=15)),(AND(A1>19.91,A1<=20)),(AND(A1>24.91,A1<=25)),(AND(A1>29.91,A1<=30))),"yes","no")

用户尝试的Cognos公式无法运行：

if(or(and([Years of Service]>4.91,[Years of Service]<5),(and([Years of Service]>14.91,[Years of Service]<15)))then ('yes') else ('null')

返回“是/否”的版本 #

if (
    or(
        and([Years of Service] > 4.91, [Years of Service] <= 5),
        and([Years of Service] > 9.91, [Years of Service] <= 10),
        and([Years of Service] > 14.91, [Years of Service] <= 15),
        and([Years of Service] > 19.91, [Years of Service] <= 20),
        and([Years of Service] > 24.91, [Years of Service] <= 25),
        and([Years of Service] > 29.91, [Years of Service] <= 30)
    )
) then ('是') else ('否')

返回布尔值（真/假）的版本 #

or(
    and([Years of Service] > 4.91, [Years of Service] <= 5),
    and([Years of Service] > 9.91, [Years of Service] <= 10),
    and([Years of Service] > 14.91, [Years of Service] <= 15),
    and([Years of Service] > 19.91, [Years of Service] <= 20),
    and([Years of Service] > 24.91, [Years of Service] <= 25),
    and([Years of Service] > 29.91, [Years of Service] <= 30)
)

1. 语法错误：if语句缺少闭合括号，Cognos中if的正确结构为if(条件) then(结果1) else(结果2)，原公式未闭合or的外层括号。
2. 条件不完整：仅覆盖了5年、15年的里程碑判断，遗漏了10、20、25、30年的条件。
3. 逻辑不一致：原公式用<5代替了Excel公式中的<=5，会漏掉刚好满5年的情况，需保持逻辑统一。
4. 返回值不符合需求：原公式返回'null'，但需求是返回“是/否”或布尔值，需替换为对应内容。

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