Re: Formula to Prorate a Quota

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