Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Rolling Value Calculation

Topic Labels: Formulas
1118 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Kristen_Gerzin
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello,

I am trying to create a table for our sales employees rolling bonus program. I have the main bones built, but I am struggling in one area.

I need a way to add values in a compound manner.

Example:

  • January they reach bonus and earn $500
  • February they reach the bonus. They would earn $500 from January + their February bonus (Example $500 + $500)
  • March they reach the bonus. They would earn $500 from January + February bonus + March bonus

I need to use the rolling values of these for their commission statements.

Does anyone have any suggestions for a formula?

Thank you kindly :slightly_smiling_face:

1 Reply 1
Mike_McLaughlin
8 - Airtable Astronomer
8 - Airtable Astronomer

Do you mean compound or running total?
(I think of compund as being where they were paid $500 in Jan, and if they hit their target in Feb, they get a $500 bonus for feb in addition to a matching bonus for all months prior)

I didn’t build it out, but assuming it is a just a running total

Have a ‘Hit-Target’ table with 2 fields Text ‘months’ and Linked Field ‘Employees’
When an employee hits their target for a month, add them to that month.

Back in another table with the employees, where there is now a ‘Target-Hit’ field, use a $500*Count() on that field.