Highlighted
New Contributor

Need help on a formula in Intermediate model building 12 section Quota modules and Rep Quota

IF NOT Active? THEN 0 ELSE IF YEAR(START()) - YEAR(REP SETUP.Start Date) = 0 THEN MONTH(START()) - MONTH(REP SETUP.Start Date) + 1 ELSE 12 - MONTH(REP SETUP.Start Date) + (YEAR(START()) - YEAR(REP SETUP.Start Date)) * 12 - (12 - MONTH(START())) + 1 

 

Please explain me this formula for tenure in Rep Quota????????

2 REPLIES
New Contributor

Re: Need help on a formula in Intermediate model building 12 section Quota modules and Rep Quota

Hi, 

As you would know for tenure we are calculating the length of time/how long each employee held their position/worked in the company. To derive this we take in the start date and end date from the given data.  Basically, the formula calculates the length by checking different conditions using month and year function.

 

IF NOT Active? THEN 0

---First condition, checks if the employee is active. Based on your example, Josh’s tenure for Jan is 0 as he is not active yet.

 

ELSE IF YEAR(START()) – YEAR(Rep Setup.Start Date) = 0

--- YEAR function converts a date to a year in number format and START function gives the first date in the period and () with START refers to current period.   Therefore based on your screen shot example for Feb, YEAR(START()) gives 2015 and YEAR(Rep Setup.Start Date) gives 2015 from Josh’s start date. Satisfying the ELSE IF condition.

 

THEN MONTH(START()) – MONTH(Rep Setup.Start Date) +1

--- MONTH Function converts a date to a calendar month as number. Similarly, MONTH(START()) gives 2 and  MONTH(Rep Setup.Start Date) gives 2. Based on this the result for Feb is 1, where +1 helps to count starting month.

 

ELSE 12 - MONTH(Rep Setup.Start Date) + (YEAR(START()) - YEAR(Rep Setup.Start Date)) * 12 - (12 - MONTH(START())) + 1

--- This condition takes in to account if start year is not the calendar year.

 

Hope this explains the formula, if you have any questions let us know.

Cheers

New Contributor

Re: Need help on a formula in Intermediate model building 12 section Quota modules and Rep Quota

thank u , it is very good explanation.