validationsyntaxreturndate-rangecognos

Years of Service Anniversary formula - Cognos Analytics - Costpoint 8.0.0


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)

i got an Excel version to work seen below:

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

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
    )