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?? :)
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
)