Split Pivot Chart Data?

[Joycelet]

Hi

I have a pivot table that produces a pivot chart but depending on which department is selected the number of staff can make the chart unreadable.

Is there away of saying have only 6 people per chart based on the selection of from the pivot table page?

Thanks for any help with this

N

 

[SkipVought]

Hi,

In your source data, make a helper column.

Sort by dept, and then add a formula that GROUPS the staff in sixes, using the IN fucntion.
[tt]
=INT((COUNTIF($A$2:A2,A2)-1)/6)
[/tt]
assuming that the Dept data starts in A2.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

...oops INT function

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[Joycelet]

Hi

I have tried put the code onto the source sheet but because the department can be repeated over 100 times this isn't returning what I need.

Below is a sample of the source data:

Group Name Start End
A NJ 01.01.07 30.01.07
A NJ 02.02.07 02.02.07
B JJ 01.01.07 30.01.07

The pivot table uses the following

Report Filter - Group
Data - Column (Formated as Month)
Name - Column
Values -

What I need is once the user has selected the group from the report filter that the graph that is produced is split out depending on the number of names on the pivot table.


I need the function to work on the pivot table before graphing as this is were the grouping takes place.

Not sure if that makes sense!

Thanks in advance

N

 

[SkipVought]

"... but because the department can be repeated over 100 times this isn't returning what I need."


The department count be repeated 10000 times, and you still get groups of 6.

Yopu have not stated exactly WHAT is being returned that is not what you need.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[Joycelet]

Hi

Thanks for the quick response - what is being returned is the following:

Group Code
A 0
A 0
A 0
A 0
A 0
A 1
A 1
A 1
A 1
A 1

etc etc. I then concate them into the Full Group:

Full Group
A - 0
A - 1

Which is great - but then when I use this in the pivot table as the Report Group it splits out my data incorrectly as each name is split over many full group.

I'm not sure I'm explaining this very well...

N

 

[SkipVought]

It takes a COMBINATION of Group and Code.

If necessary, concatenate Group and Code and use that value in the pivot.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

Please post an example, showing BOTH source and PT result.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[Joycelet]

Hi Skip

Below is a sample of the raw data:

Resource_Group Resource_Names Start_Date Finish_Date % Complete 01/08/2007 01/09/2007 01/10/2007 01/11/2007 01/12/2007
A NJ 12/12/2007 11/01/2008 0% 0.00 0.00 0.00 0.00 15.00
A JJ 27/08/2007 01/02/2008 60% 5.00 21.00 24.00 22.00 22.00
A NJ 08/01/2008 21/01/2008 0% 0.00 0.00 0.00 0.00 0.00
A JJ 03/09/2007 01/10/2007 0% 0.00 21.00 1.00 0.00 0.00
A NJ 08/10/2007 14/11/2007 0% 0.00 0.00 19.00 10.00 0.00
A JJ 22/01/2008 04/02/2008 0% 0.00 0.00 0.00 0.00 0.00
A NJ 22/01/2008 31/01/2008 0% 0.00 0.00 0.00 0.00 0.00
A JJ 05/02/2008 25/02/2008 0% 0.00 0.00 0.00 0.00 0.00
A NJ 19/12/2007 08/01/2008 0% 0.00 0.00 0.00 0.00 10.00
A JJ 14/11/2007 11/12/2007 0% 0.00 0.00 0.00 13.00 7.00
A NJ 11/12/2007 19/12/2007 0% 0.00 0.00 0.00 0.00 7.00
A JJ 17/09/2007 15/01/2008 50% 0.00 11.00 24.00 22.00 22.00
A NJ 23/04/2008 09/05/2008 0% 0.00 0.00 0.00 0.00 0.00
A JJ 07/04/2008 30/05/2008 0% 0.00 0.00 0.00 0.00 0.00
A JJ 02/10/2007 15/10/2007 0% 0.00 0.00 10.00 0.00 0.00
A JJ 16/10/2007 09/11/2007 0% 0.00 0.00 13.00 7.00 0.00
A JJ 04/12/2007 11/12/2007 0% 0.00 0.00 0.00 0.00 6.00
A JJ 12/11/2007 15/11/2007 0% 0.00 0.00 0.00 4.00 0.00
A JJ 16/11/2007 21/11/2007 0% 0.00 0.00 0.00 4.00 0.00
A JJ 22/11/2007 27/11/2007 0% 0.00 0.00 0.00 4.00 0.00
A JJ 28/11/2007 03/12/2007 0% 0.00 0.00 0.00 3.00 1.00


And here is a sample of the pivot table:

Data Resource_Names A
Aug-07 NJ 0
JJ 2
Sep-07 NJ 0
JJ 11
OCt-07 NJ 19
JJ 291
Nov-07 NJ -180
JJ -6348
Dec-07 NJ 32
JJ 138388
Jan-08 NJ -162
JJ -4839274


Hope this makes sense - not sure if you can attached files to the threads?

Thanks for all help so far

N

 

[SkipVought]

You have to understand that I do not know what you are expecting.

You threw an example out there without ANY explanation about what it is that is not correct. What do you get? What do you expect?

Where is A-0, A-1 etc. Isn't that what we have been talking about?

?????

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[Joycelet]

Hi

I'm sorry that my explanation wasn't very good - I'll try again.

The raw data is used to create a pivot table that takes the Resource Group as the Report Filter. The pivot table then shows the dats by months in the rows and by Status in the columns.

This is then used to plot a graph - all works great but the graph is unreadable due to there being to many people in each Resource Group. So I need to work out at the pivot table stage, as this is were the data starts to become meaningful, how many people are in each group and then split 5 people per graph per group.

Does this make anymore sense?

N

 

[SkipVought]

Where is the column containing the GROUPING data that we have been talking about 0, 1, 2, 3 etc, so that it will limit the number of items displayed to SIX????

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[Joycelet]

Hi

Sorry it's a hidden column - Column C.

N

 

[SkipVought]

Please do not be sorry!!!

Please post CLEAR, CONCISE and COMPLETE examples.

Otherwise this exchange of words is completely wasted!!!

I asked you to post an exmple that demonstated the problem. So far, I have not seen the problem demonstrated by the data.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

Also, be absolutely sure that the SOURCE DATA and the PivotTable contain CONSISTENT data. For instance, where are the negative numbers coming from in your PT where there are no negative number in your source data?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[Joycelet]

Hi

Thanks for all your help - I was being stupid and had the formula in the wrong spreadsheet. All working as should now!

Again Thanks
N