Years of Service Anniversary formula - Cognos Analytics

this one has been bugging me for a few weeks... I'm trying to write a formula in Cognos Analytics (costpoint) that returns if someone is hitting a new years of service milestone in the actual month.

returning a simple "true/false" or "yes/no" is perfect

essentially it's just if their years of service fall between multiple date ranges (ex: i want a return value of "yes" for someone currently at 4.95 years of service since they would hit their 5 years within the coming month)

years of service are in number format in column "A" in excel and in column [years of service] in costpoint (cognos) (ex: 9.154, 4.982, 24.995 ...)

i got an Excel version to work seen below:

=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")

i'm still just getting familiar with Cognos(costpoint) syntax, so i tried to write it as seen below:

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')

without any luck...

anyone want to take a crack at it?? :)

CodePudding user response：

Look at the various case functions and try them for your expression.

CodePudding user response：

In the absence of start dates, which would be easier, and handling the more general case (What if they are approaching 45 years of service?):

case
  when MOD(MOD([Years Of Service], 5)   1, 5) > 0.91
   and MOD(MOD([Years Of Service], 5)   1, 5) <= 1
    then 'yes'
  else 'no'
end