Need help on intermediate model building course , 12 section Rep quota part

pradeep

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 for Rep quota?

 

Screenshot_(884)_39686.png

LipChean_Soh

Hi Pradeep,

 

While we employ different approaches to solving problems in Anaplan, normally when it comes to understanding a formula, i would just copy and paste the original formula in a notepad, and slowly add back small parts of the bigger original formula back in Anaplan to see what the formula comes back with.

 

 

Having said that, let me try to explain the formula below:

IF NOT Active? THEN     

     0

ELSE

         IF YEAR(START()) - YEAR(REP SETUP.Start Date) = 0 THEN   => If the Rep's setup year is the same as the item(time)'s year

               MONTH(START()) - MONTH(REP SETUP.Start Date) + 1  => then tenure = month difference

         ELSE

              12 - MONTH(REP SETUP.Start Date) + (YEAR(START()) - YEAR(REP SETUP.Start Date)) * 12 - (12 - MONTH(START())) + 1   => otherwise calculate the tenure =  month difference + (year difference * 12), because there's 12 months in 1 year

 

To be fair, the last formula can be changed to 

"MONTH(START()) - MONTH(REP SETUP.Start Date) + 1 + (YEAR(START()) - YEAR(REP SETUP.Start Date)) * 12"

 

Thanks,

LipChean