Can you create a formula field that looks into other records both forward or backward of the same table?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I am trying to track sales velocity for our sales reps. I have a Sales Representitive table, a Leads table and an Opportunity table. When a Sales Rep gets a sale, we enter in a "Date Closed" date in the leads table and move the lead from the Leads table to the Opportunities. Sales Reps earn more money based upon sales velocity. 

Here is my issue. I have created a summary field (Sales Representitive table to Lead table) that counts "Date Closed" for the past 30 days, which tells us how many sales the rep has closed within the last 30 days. I am then doing a lookup on the summary field into the opportunities table and I have a dynamic form rule in the opportunities table that captures the number counted by the summary field for the running 30 day sales velocity the day that it closes. My issue is when a sale cancels, I am unable to figure out a way for the corresponding records to update so that we don't overpay a sales rep on jobs that should be adjusted due to a prior cancellation. The problem with the summary field is that it is counting all records with the "Date Closed" after 30 days in the past which means it is constantly moving forward.

Essentially I need a formula field that I can create in each record that looks 30 days in the past from the date of the record and not the current date such as the summary field does. Is this possible or is there another work around to better track real time sales velocity that also takes into consideration any prior jobs (records) that may have canceled in the past? 

Photo of Nathan

Nathan

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Neal

Neal

  • 70 Points
Hi Nathan,

I am Trying to visualize your data - sample screenshot prototype is attached.

I think there are two trigger points for your calculation.

  • When Sale is done
  • When Sale is canceled

Above two events can trigger re-calculation of Sales Velocity.

The other part is just defining monthly processes.

  • At the beginning of the month initialize your calculation-velocity.
  • At the end of the month close the calculation-velocity and record calculation together with supporting data.This data will be used to pay the sales-rep. It should be kept so that it is always current.
  • When Sale is done recalculate current calculation-velocity.
  • When Sale is canceled recalculate current calculation-velocity.

There should be defined process regarding how cancellation affects calculation-Sales-Velocity.

It simplifies the process by not having limit of 30 days window and will be accurate because it is triggered at the events of sale and Sale cancellation


The processes should be defined in such a way so that there are no gaps - i.e. each sale and each cancellation should be considered for calculation of Sales-Velocity.

Thanks,

Neal

NealPatil @ gmx.com