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

pradeep
pradeep
in Modeling

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?

 

Tagged:

Answers

  • LipChean_Soh
    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

Categories