how to decide the completed years(Length of service)

edited May 6 in Modeling

hi guys~

In Sales_Rep_Details module, What we know is the sale start date, so I calculate the completed years by:

Start_Date_Current_Period(2020-01-01) - Start Date

then I devide it by 356

the final formula is

ROUND(('SYS01 Time Settings by Month'.Start_Date_Current_Period - Start Date) / 365, 0, Up,NORMAL)

by compare my module with the given sample, They seem all the same, but,

it turns wrong

SO, I assume It is the problem of number of completed years.

would you please tell me the the right way to calculate it?

or, May be the issue of Role or Tenure?

It is best of giving same sample capture of it

Is my module right?

thank you

———————————————————Update 2023/5/6——————————I change formula of Completed Years to

ROUND(('SYS01 Time Settings by Month'.Start_Date_Current_Period - Start Date) / 365, 0, NEAREST, NORMAL) than it matched.

but, tenure bacome unmatch.

but again, I think the sample is wrong, if current period is 2020 Jan, 2020/10/1 should be not start.

what's your opinion? thank you

Best Answer

  • Venki9902
    edited May 6 Answer ✓

    Hi @Livy ,

    Yes, a Value less than or equal to 0 should be "not started?".
    Check your tenure line-item formula once.

    Attaching a screenshot for your reference.

    As per your screenshot even for 0 it is <1 yr and check correct it for 0 it should be Not started?.

    Hope this helps :).



  • Hi @Livy ,

    The formula for completed years will be like below:


    Hope this helps :)



  • thank you @Venki9902

    but how about the situation like this


    YEAR(Start Date)=YEAR(2020/10/31)=2020


    but it should be "not started"