Discussions

Expand all | Collapse all

Formula to Prorate a Quota

  • 1.  Formula to Prorate a Quota

    Posted 03-12-2019 20:19

    I need to figure out the best method to set this up.  It hurts my brain when I attempt to solution it, so I am hoping someone can help simplify.

    I need this to calculate a prorated quota amount based on
    the following:

    Annual Quota:  (variable)

    Plan Start Date:  (variable, but always 1st of the month)

    Plan End Date:  (always 12/31/2019 annually)

     Each Quarter has a variable % to determine the prorated amount:   Q1, Q2, Q3 = 20% of annual Quota + Q4 = 40% of annual quota

     So as an example, a person with a total annual quota is $100,000 for the year starting on 3/1/19 would have 1 month in Q1+ the remaining Q2 ? Q4 calculated by the % per above.

    Calculates as follows:  Q1 ($100,000*20%/3 months*1 month) = $6,667 + Q2 ($100,000*20%)= $20,000 + Q3 ($100,000*20%)= $20,000 + Q4 ($100,000*40%)= $40,000 = $86,667 Prorated Quota for the year.   

     I want the user to be able to add the Start Date, total Annual Quota and check a box ?Prorate Quota?? and it will return the $86,667 in the Prorated field. 




  • 2.  RE: Formula to Prorate a Quota

    Posted 03-12-2019 21:50
    So this is a down and dirty version - did some testing though and it matched your example and a couple other tests:

    I'll try and add a couple notes for one example as well

    //START

    var date start = ToDate([Start Date]);
    var date startD = FirstDayOfMonth([Start Date]);
    var date yearEnd = ToDate("12/31" & Year($start));
    var number quote = [Quota];

    //Q1
    ($quota * 0.2) * (If( $start <= ToDate("3/30/" & Year($start)), Min(3, (4-Month($start))),0)/3) +  
    //Q2
    ($quota * 0.2) * (If( $start <= ToDate("6/30/" & Year($start)), Min(3, (7-Month($start))),0)/3) +
    //Q3
    ($quota * 0.2) * (If( $start <= ToDate("9/30/" & Year($start)), Min(3, (10-Month($start))),0)/3) +
    //Q4
    ($quota * 0.4) * (Min(3, (12-Month($start)))/3)

    //END

    //Q1
    //First set your quota against the multiplier for Q1

    ($quota * 0.2) *

    //Then check to see if they were actually active during Q1 by comparing the end of Q1 in the current year against their start date

         If( $start <= ToDate("3/30/" & Year($start)), 

    //If they were active during Q1 - figure out how many months they were active for

    Min(3, (4-Month($start)))

    //In a nutshell, it says from the end of Q1 - subtract what month they started in
    //So if they started in February, this would read as Min(3,(4-2)) which returns 2, counting the 2 months in Q1. 

    //Finally - close out your if() saying that if they weren't active in Q1, do nothing, and then divide that by 3 to get your actual % of the month value.

    ,0)/3)

    **Another example of the Min() function -
    If someone started in February, and you were looking at Q2 - it would read like

    Min(3, (7-2))=> Min(3,5) => the full 3 months in Q2

    Hopefully the explanation is not as confusing


    Chayce Duncan | Technical Lead
    (720) 739-1406 | chayceduncan@quandarycg.com
    Quandary Knowledge Base


  • 3.  RE: Formula to Prorate a Quota

    Posted 03-13-2019 13:53
    Wow,thanks Chayce!  I think it will work.  I just need to figure out to adjust the for the date field.  The first var date is good with a datetime field but the second won't take datetime.  how do I adjust?




  • 4.  RE: Formula to Prorate a Quota

    Posted 03-13-2019 13:57
    I don't think you actually need that after I looked after my initial comment. I wrote that in there when I first started thinking through it but then when I got to the end didn't end up needing it. You can just remove it to avoid confusion. 

    For reference though - since plan start date 2 is a date/time - you would just convert it to a date to have Quick Base look at it correctly. Like so:

    FirstDayOfMonth(ToDate([Plan Start Date 2]))


  • 5.  RE: Formula to Prorate a Quota

    Posted 03-13-2019 14:32
    Got it! Thank you. I have it being fully tested, but kicked the tires quickly and it seems to be right on.  Very much appreciate the assistance!