Joined: 06 Feb 2012
|Posted: Wed Feb 08, 2012 4:36 am Post subject: How many customers have my sales reps visited?
I have sales reps visiting our customers. I want to know how many unique customers each sales rep has visited.
This is what the data looks like:
Col A: Sales Rep
Col B: Customer
Col C: Visit date
How do I get Datapilot to display the number of visited unique customers per sales rep?
I have tried Example 1:
This is clever, it shows the last date any given customer was visited.
Example 3 shows when was the last time Sales Rep X has visited Customer Y.
But I can't get to unique customer count.
How do I get Datapilot to show that Chuck has visited only 2 unique customers, Martin has visited only 3 unique customers, etc? How do I get Datapilot to do that without resorting to formulas such as CountA under each column? That is, how do I get the total result in, say, E14 to display a count AS IF I had written the formula
to E14 in Example 3 ?
I want to avoid formulas because they do not automatically expand to include new customers and sales reps and could also be overwritten by an expanding Datapilot.
Getpivotdata() function does not seem to have features for returning values such as subsection height and width, which I could have used to harvest the number of unique values by looking at the subsection widths.
I have spent the last 3 full workdays looking through this forum and tutorials, I tried a lot of combinations, but I'm still unable to find a solution. I'd appreciate any help.
Thank you for reading.