SalesName | Customer | Start Date | Action Date | Quick | Medium | Long |
---|
Jack | ABC | 1/5/2012 | 1/6/2012 | 1 | 0 | 0 |
Jack | ABC | 1/5/2012 | 1/25/2012 | 0 | 0 | 1 |
Jill | XYZ | 1/5/2012 | 1/5/2012 | 1 | 0 | 0 |
Jill | XYZ | 1/5/2012 | 1/6/2012 | 1 | 0 | 0 |
Jack | ABC | 1/15/2012 | 1/25/2012 | 0 | 0 | 0 |
James | BBB | 1/15/2012 | 1/29/2012 | 0 | 1 | 0 |
Here's my issue: I have a group of ten salespeople. I want to measure how quickly they respond when they get a lead. I have a straight table, as shown above. The fields "Quick", "Medium", and "Long" are calculated using an expression (NetWorkDays). I want, for each rep, to do a count distinct of company, a count distinct of Start Date per company, and a count of Quick, Medium, and Long. This will, I hope, tell me for each rep the number of companies they are working, the number of opportunities they have, and how many total calls they have made that fall into each of the Quick, medium, and long buckets. I've tried doing this as a pivot table, but it won't let me sum on the expressions, and when I tried a partial sum for Company, it didn't show any sums for Quick, Medium, or Long. I should think this would be simple - it'd take me a minute in Excel - but clearly I'm missing something here. Any help?