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 (
            _add_months (current_date, 1)
        ), 
        _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 (
            _add_months (current_date, 1)
        ), 
        _first_of_month ([StartDate])
    ), 
    60
)

Answered By – dougp

Answer Checked By – Marie Seifert (AngularFixing Admin)