# Years of Service Anniversary formula – Cognos Analytics – Costpoint 8.0.0

## Issue

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?? ðŸ™‚

## Solution

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
``````

To see who reaches a "years of service" value that is a 5-year milestone next month, create a filter:

``````mod(
_months_between (
_first_of_month (
),
_first_of_month ([StartDate])
),
60
) = 0
``````

But if you have service start dates, you can use dates calculations to see who reaches a 5-year milestone next month:

``````mod(
_months_between (
_first_of_month (