Formula to Prorate a Quota

• 0
• Question
• Updated 3 months ago
• In Progress
• (Edited)

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.

• 284 Points Posted 3 months ago

• 0
• 4,122 Points 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

(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base
• 284 Points 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,122 Points 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]))
• 284 Points 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!