Charting data against dates where dates are not at fixed intervals

[PK]

I have several data points that are the result of a pivot-table and I'm
trying to plot them in a pivot-chart (I can easily get them out of the pivot
table if the graph format is not supported for pivot-charts). Here's an
example of the data:

2005/01/15 2005/02/22 2005/03/18 2005/05/20
X 16 18 22 12
Y 4 6 3 2
Z 14 19 12 34

What I'd like to do is plot the data (preferably on a line chart) such that
the dates are on the x-axis and spaced as they would be on a calendar and
not simply as 4 points on a chart. e.g. in the example above there would be
4 points per category but the points would be at varying intervals on the
date axis as they are not equidistant in time. Is this possible?

All help appreciated?

Regards,
PK

 

[Tushar Mehta]

Did you try the default chart that XL creates based on your PivotTable?
Once you have that, if it is not already a Line chart, change the type
(select the chart, then Chart | Chart Type...). If the x-values are
not correctly spaced, click the chart then Chart | Chart Options... |
Axes tab. In there set the type of the x-axis to 'Time scale'.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Unfortunately, one of the many limitations of pivot charts is that they
only allow a purely categorical category axis, and no time-scale axis.

You could include records in your pivot table source data range that
include the missing dates but have blanks for the actual data. You'll
have to right click on the date field header, choose Field Settings from
the context menu, and check Show Items with No Data.

Or you could make a regular chart from the pivot table:
- select a blank cell which is not connected to the pivot table
- start the chart wizard, choose the chart type in step 1
- click on the Series tab in step 2, and enter the name and values for
each series separately

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[PK]

Did you try the default chart that XL creates based on your PivotTable?
Once you have that, if it is not already a Line chart, change the type
(select the chart, then Chart | Chart Type...). If the x-values are
not correctly spaced, click the chart then Chart | Chart Options... |
Axes tab. In there set the type of the x-axis to 'Time scale'.

Tushar, this did not work ... it changed all my dates from e.g. 1/26/2004 to
1/1/1900 and the next date to 1/2/1900 (IIRC) - I think it just took the
'category number' and made it a date.

Regards,
PK

 

[PK]

Unfortunately, one of the many limitations of pivot charts is that they
only allow a purely categorical category axis, and no time-scale axis.

You could include records in your pivot table source data range that
include the missing dates but have blanks for the actual data. You'll have
to right click on the date field header, choose Field Settings from the
context menu, and check Show Items with No Data.

Or you could make a regular chart from the pivot table:
- select a blank cell which is not connected to the pivot table
- start the chart wizard, choose the chart type in step 1
- click on the Series tab in step 2, and enter the name and values for
each series separately

Thanks Jon, this is what I ended up doing and it looks pretty good ... now
if only I knew how to set the default line width and backgrounds on all
charts to something I can see more easily I'll be set. It's quite a lot of
work to format ~30 charts changing backgrounds and increasing line width on

5 series per chart.

Thanks again,
PK