Forum Discussion
Weight values over time
Has anybody figured out how to do this?
I want to weight the number of sales reps we have based on the length of time they've been selling for us. I have a ramping schedule similar to the simple example below (this would say a rep is fully-ramped at month 12):
Month 1: 1/12
Month 2: 2/12
Month 3: 3/12
Month 4: 4/12
Month 5: 5/12
Month 6: 6/12
Month 7: 7/12
Month 8: 8/12
Month 9: 9/12
Month 10: 10/12
Month 11: 11/12
Month 12: 12/12
Month 13: 1
Month 14: 1
...
I need to do some calculations based on a table of their names and start/end dates as reps:
Rep Name | Start Date | End Date
Johnny | 1/1/14 | Null (Still selling for us)
Billy | 1/1/14 | 6/30/15
Jimmy | 4/1/15 | Null
Craig | 12/1/15 | Null
This list is coming from a connection so as we add new reps, the table will continue to expand.
I want to know how many weighted reps we have in any given month. Based on the 4 sample reps above, I would have:
2/12 of a rep for January 2014
4/12 of a rep for Feb 2014
...
2 1/12 reps for April 2015
2 2/12 reps for May 2015
...
Any ideas on how this can be written in measures/columns?
5 Replies
Bump
You could do it by:
- Creating an attendance table so there would be 1 row for each rep per month (you could go down to day level if you have the data)
- Each month append a new set of data for reps still with you
- Include a column with their "score" i.e number of months since start date/12 with a max value of 1.
Something like
Month|Rep_ID|Score
1|1|1
1|2|0.2
2|1|1
2|2|0.3
Then just aggregate the scores by months. This approach would also allow reps to have gaps in thier employment i.e. sickness etc.
Thanks, Itchy. The Attendance table is the part I'm struggling with. Any ideas on how to build that when all I have to start is the rep and the dates?
