Need assistance with calculation based on a dropdown date field
Newbie user and creating a loan database that calculates loan balances, pretty much have the loan balance issue figured out, calculates properly etc. In addition to the loan balance, would also like to show a running total of the person’s commissions that are coming in that have not yet been consumed, or their “pipeline” . The total would be based on the user’s selection of a date in a drop down calendar field.. So an individuals running total of their commission pipeline based on that selected date. The table consists of commissionable events, each has an “end date” here is an example of what I am trying to achieve:
Event # 1 end date = 12/31/2010
Event # 2 end date = 1/15/2011
Evemt #3 end date = 1/31/2011
Event # 4 end date = 5/15/2011
User selects a date of 1/1/2011 on drop down calendar field, total commission field would be the sum of events 2 through 4. Likewise if user selects date of 1/25/2011, field would be the sum of events 3 and 4
Not sure if it should be a number field based on a calculation or a calculation field also, not sure of the function or formula I should use.