Pivot Table 2010 COUNT DISTINCT Salesman

[SalimJ]

Hi,

I have a Excel list for Sales data with 1 record per salesman/period.

I have created a pivot table in compact form from that list where in the Report Filter section, I have the period (year/month). In the Row Labels section, I have Cluster/Dept/Salesman. In the Values section, I want to display #Salesman, Total Sales, etc.

What I want to do is: whatever the number of periods I choose from the Report Filter, the #Salesman must display the correct number of salesmen. Actually, it is displaying the number of salesmen * the number of periods.

Any idea on how to solve this problem.

Many thanks.

Regards,
Salim.

 

[nuked]

If you have each sales man listed down the side, how can you hope to show the number of sales man per period in the same data?

 

[SalimJ]

Thanks for your quick reply.

Is there a way to upload a sample file to illustrate why I'm trying to explain?

Thanks.

 

[nuked]

You can uploade to any free file share site and post the link here. Personally, I'm unable to access such sites in my current environment, but another expert might be able to help further.

 

[Jerry Sullivan]

Salim, If you'll post to a hosting site, or send me a PM with your email address, I'll look at your example file and try to help.

In most cases, an SQL query can be used to aggregate like records so that the PivotTable's count function will return distinct counts.

I understand Nuked's point though and it's not clear if you are trying to have both count's by period and unique counts for all periods displayed at the same time.

 

[Marcelo Branco]

Salim, If you'll post to a hosting site, or send me a PM with your email address, I'll look at your example file and try to help.

In most cases, an SQL query can be used to aggregate like records so that the PivotTable's count function will return distinct counts.

I understand Nuked's point though and it's not clear if you are trying to have both count's by period and unique counts for all periods displayed at the same time.

Hi Jerry,

If you achieve a solution to this case, please, let me know. I'm interest in learning more about these situations using SQL.

Are you aware about this?
Distinct Count in Pivot Tables

Regards,

Marcelo

 

[Jerry Sullivan]

Hi Marcelo,

Our company isn't using xl2013 yet, and I wasn't aware that a Distinct Count feature had been added.

That's a great improvement. Thanks for sharing that!

Since Salim is using xl2010, he'll need a work around. He sent me his file and what's probably needed is a Parameter Query each time the Report Filters for dates are updated. I'm busy now, but I'll post a suggestion later today.

 

[Marcelo Branco]

Ok.

M.
ps: I also do not have access to Excel 2013 yet. But now we have a very good reason for asking for an update

 

[Jerry Sullivan]

Hi Salim,

This is a more challenging problem than I had anticipated!

The query to aggregate the data from the selected periods was straight-forward enough.

The problem with that approach that I had not anticipated, is that once you have combined the data for the selected periods (let's say (2013/08 and 2013/09), you no longer have any of the other Periods in the filtered datasource. Therefore, after the first query, you would need some other means to restore the list of all available Periods in the raw data to your Report Filter.

I considered several workarounds that each had its own drawbacks, including...

• Using a multi-select ComboBox to select the Periods
• Adding dummy records and populating them with all unique Periods in the raw data
• Clearing filters from the previous filter using a query before each time the user selects new Periods.

The one I liked the most (or disliked the least) in terms of User Interface was to use a Report Filter from a "Dummy" Pivot and place that Report Filter above the Report Filters of your actual PivotTable.

The code uses an added Sheet "QueryResults" which could be hidden if you prefer.

This solution seems undesirably complicated, but I can't think of a way to achieve the same result without negatively impacting the user interface. Marcelo had the best solution: Use Excel 2013!

I've sent the file to you via email for you to try. I'll add a separate post here shortly with the code and some setup instructions.

 

[Fazza]

hello, Salim

It would help if some sample data is posted. Both input data & corresponding output data.

regards