automate charting

  • Thread starter Thread starter ooievaar6
  • Start date Start date
O

ooievaar6

To continue the analogy:

The four columns of numbers represent the different activities of the
fruit eg. in stock, on order, sitting on the shelf, sitting by the
counter. The first two columns of numbers are in thousands, and so get
plotted against the left y-axis. The last two columns of numbers are
in tens, and so get plotted the right y-axis.

The month column is plotted along the x-axis.

Is the answer still a PivotTable with an associated PivotChart?

Rgds,
Ooi
 
Ooi -

Use a Pivot Table, with a Pivot Chart or a Regular Chart.

Put headers on your data (which is already ideally set up for pivot
table analysis, by the way, good job):

Fruit Date Stock Order Shelf Counter

Now make a pivot table from the range, with Fruit in the page field,
Date in the Column field, and Stock, Order, Shelf, and Counter in the
Data field. The table looks like this:

Fruit apples

Date
Data Jan-03 Feb-03 Mar-03 Apr-03
Sum of Stock 1000 2000 1010 2010
Sum of Order 2000 3000 2020 5000
Sum of Shelf 3 4 1 6
Sum of Counter 4 4 1 6

Change the selected fruit in the Page field dropdown and the table and
chart will update.

- Jon
 
Jon et al.

Thanks for introducing Pivot Table/Chart!

I got to the stage you described, producing a default "composite?" bar
chart for Stock, Order, Shelf, and Counter plotted on x-axis, and Date
plotted on y-axis.

Try as I may, I couldn't get the chart I originally wanted, i.e. for
each Fruit, a 2-axis line chart showing Date on the x-axis, Stock and
Order on left y-axis, and Shelf and Counter on right y-axis.

I also tried sorting my original data by Date, somehow that produced
an even more "nested" pivot table. Perhaps I need to "flip" (swapping
rows with columns) the original data? If so, how can I do that? Or did
I just miss something obvious?

Another question a little ahead: once successful, how would I go about
making static (or linked) "dumps" of each of the charts, ie. to get a
bird's eye view of all the charts in eg. one spreedsheet?

-Ooi
 
I apologize if this is a double posting, I got an error message after
hitting "Post message".



Jon et al,

Thanks for introducing Pivot Table/Chart.

I got to the stage you described, producing a default "composite?" bar
chart with Stock,Order,Shelf,Counter displayed on x-axis and Date on
the y-axis.

Try as I may, I couldn't get the original chart I wanted, ie. a 2-axis
line chart for each Fruit whre Date is displayed along x-axis, while
Stock and Order is displayed against the left y-axis, and Shelf and
Counter are displayed against the right y-axis.

I also tried sorting the original data by Date, but this created an
even more "nested" pivot table. Do I need to "flip" (swapping rows
with columns) the original data, and if so how can I do this? Or did I
just miss something obvious?

Another question a little ahead: Once successful, how can I make a
static (or linked) "dump" of all the charts, so I can get a bird's
eye view of them eg. in a single spreadsheet?

-Ooi
 
Did your pivot table look like what I posted? Make a line chart of the
pivot table data, with the series in rows. The chart has two lines up
where you can see them, and two along the X axis. Double click one of
the low lines, and on the Axis tab, select Secondary. Select the other
low line and press the F4 key to repeat the last action.

- Jon
 
Jon,

Yes, my pivot table looked like what you posted and yes I now get the
chart I wanted.

Here's what happened:

I had a read of Pivot Chart tips & troubleshooting at your website,
and clicked on a cell away from the Pivot Table and created an empty
chart.

My error was in highlighting all 5 columns (for apples) and drag+drop
them into the empty chart. This always created a chart with Date
plotted along the y-axis.

After your answer, I had another go, and eventually found that to set
the data range, I needed to highlight only from the 2nd column
onwards, ignoring the first column containing "Data", "Sum of stock",
"Sum of order", "Sum of shelf", "Sum of counter". The Date is now
plotted along the x-axis, but I had to "manually" label the "Series
1",etc. This isn't really a problem, as I would only have to do it
once. To get a 2-axis chart, I just change the Chart Type.

I can live with the current solution, but would like to know the
difference of the two methods above. Is the second chart-type also
called a Pivot Table? The first chart-type had a toggle/pivot in it,
while the second chart-type is updating based on a toggle/pivot
setting at the Pivot Table. Is it possible to get the first
chart-type with the Date plotted along the x-axis?

How about getting a "dump" of all charts into a single spreadsheet?

-Ooi.
 
Back
Top