Ignoring Series in Graph

Posted by Ben Posaner on May 01, 2001 4:21 AM

I'm having a problem with a graph that's built from data in a pivot table.

Basically I've built a pivot table and used the wizard. I've put my Variable where I want and then used the page option. This builds the pivot table fine and then I build a graph from the data in the pivot table. (The graph is all data). Then if I select the item from the "dropdown" box it rebuilds the table with the vaules that only belong to the item selected in the drop down box. This works fine and the graph changes to reflect only these items. However the graph still contains "blank series" which are shown in the legend.
Is there a way, without going into the graph and changeing/removing series, to just have the series shown in the pivot table.
Note: The choices in the drop down box have different number of variable.

I hope I haven't confused you all.

Thanks

Posted by Ben Posaner on May 01, 2001 4:42 AM

Had an idea, can you tell the graph to ignore blanks?

Posted by Mark W. on May 01, 2001 6:01 AM

Ben, are you sure that you've selected the entire
PivotTable before creating your graph? If you
haven't the chart won't dynamically adjust as the
PivotTable content changes. To be sure that you've
selected the entire PivotTable I recommend that you
use the "Select Label and Data" selection tool which
you may need to add to your PivotTable toolbar.

By the way, what ever happened on your earlier
posting about rows that disappeared when you
changed your PAGE field?

Posted by Ben Posaner on May 01, 2001 6:34 AM

Mark

The answer is yes, I have selected the entire table before i created the table. But what I think might mess the dynamically changing series is that I've split up the graph.

Let me explain:-Basically I created the pivot table with,
Rows of "Date-Hour"
Cols of "Group"
Page of "Band"
And filled with sum of values.

I then created a graph of the entire Pivot table.
I need to split the data so I would have one graph for each week (my data has 5 weeks of "date-hour" data) So I tried pointing the graph at the relevent week, but it didn't like the pivot table. So I build a macro that changed the series pointers and it gave me the first weeks graph. & the i did the same for the second week. & so on.

But this way looses the dynamically changing series. Have I done the wrong thing if i need to select parts of pivot tables?

Is it possible to write a bit of VBA that take the Legend and hides the "blank" series? I know its not perfect but its an answer.

By the way I sorted out my problem with "rows disappearing, thanks for asking. It was a box I had'nt seen!! Typical.

Thanks, and I hope its clear!!!

Posted by Mark W. on May 01, 2001 7:36 AM

Ben, can you insert a week column (or a
Week-Date-Hour column) into your data
set and then modify your PivotTable to
include it?

Posted by Ben Posaner on May 01, 2001 8:59 AM

Yes I can add a week column

Mark

I've added a new variable "week" (values 1-5) to my initial data, So now I've got a pivot table with "week" & "date-hour" as rows, "group" as columns & "band" as page.( and my data summed)

So can I build dynamically changing graphs for each week?

Thanks

Ben

Posted by Mark W. on May 01, 2001 11:27 AM

Re: Yes I can add a week column

Yes... each category on your X-axis would be a
an unique instance of 'Week' and 'Date-Hour'. Is
that what you want? I've added a new variable "week" (values 1-5) to my initial data, So now I've got a pivot table with "week" & "date-hour" as rows, "group" as columns & "band" as page.( and my data summed) So can I build dynamically changing graphs for each week? Ben

Posted by Ben Posaner on May 02, 2001 12:48 AM

Nearly There!

MArk

Could I have 5 seperate graphs (1 for each week?)

Ben

Posted by Mark W. on May 02, 2001 7:08 AM

Re: Nearly There!

Sure! You could create a PivotTable of each of
your5 weeks and generate separate charts from each
PivotTable. MArk Could I have 5 seperate graphs (1 for each week?) Ben