Keep complete date series on X axis

I have data grouped by month, as follows:
Customer Month1 Data1 Data2 ....
Customer Month2 Data1 Data2....
....... ...... ..... ......

I am creating a pivottable & chart to show customer data series, with the
months on the X axis. Works well, but when I am displaying a series that
does not have data for all months, the absent months are left off the X axis
(logical enough: how should Excel chart data that doesn't exist?). What I
want is for the X axis to always show all months, regardless of the data
i.e. there should be gaps in the data for missing months, as there is when
multiple series are shown. Relevent point: I am running all of this from
VBA code, including the copying in of a recordset from an Access database.

This doesn't seem like it should be such a hard thing to do, but I have
researched this every which way, and can't find a good way to do it. The
only solutions I have thought of are:
1. Loop through my recordset and add customer & month entries (with missing
data) for all missing months (Ugh).
2. Some kind of voodoo with a dummy series (haven't figured this one out
fully though).

I would be really, really grateful for any pointers.
TIA

Jeff:

It sounds like you are using a line chart, not an XY chart.

Line charts treat the X axis data as categories, even if the data numeric.

XY charts threat the X axis data as numbers and plot them based on numeric
value.

Check your chart type.

For trend charts and chart types, you may want to look at this page.

http://processtrends.com/pg_charts_trend_chart.htm

[email protected]



"Jeff Eckermann" <[email protected]> wrote in message
news:[email protected]...
>I have data grouped by month, as follows:
> Customer Month1 Data1 Data2 ....
> Customer Month2 Data1 Data2....
> ...... ...... ..... ......
>
> I am creating a pivottable & chart to show customer data series, with the
> months on the X axis. Works well, but when I am displaying a series that
> does not have data for all months, the absent months are left off the X
> axis (logical enough: how should Excel chart data that doesn't exist?).
> What I want is for the X axis to always show all months, regardless of the
> data i.e. there should be gaps in the data for missing months, as there is
> when multiple series are shown. Relevent point: I am running all of this
> from VBA code, including the copying in of a recordset from an Access
> database.
>
> This doesn't seem like it should be such a hard thing to do, but I have
> researched this every which way, and can't find a good way to do it. The
> only solutions I have thought of are:
> 1. Loop through my recordset and add customer & month entries (with
> missing data) for all missing months (Ugh).
> 2. Some kind of voodoo with a dummy series (haven't figured this one out
> fully though).
>
> I would be really, really grateful for any pointers.
> TIA
>

"Kelly O'Day" <[email protected]> wrote in message
news:[email protected]...
> Jeff:
>
> It sounds like you are using a line chart, not an XY chart.
>

It's a pivot chart. Very convenient in some ways, but not very driveable,
as I am finding out.

> Line charts treat the X axis data as categories, even if the data numeric.
>
Ok: but I had the impression (based on some other posts in this group) that
line charts handle temporal data just fine...

> XY charts threat the X axis data as numbers and plot them based on numeric
> value.
>
> Check your chart type.
>
> For trend charts and chart types, you may want to look at this page.
>
> http://processtrends.com/pg_charts_trend_chart.htm

Interesting: thank you. Looks like the dummy series solution will be best.

>
> [email protected]
>
>
>
> "Jeff Eckermann" <[email protected]> wrote in message
> news:[email protected]...
>>I have data grouped by month, as follows:
>> Customer Month1 Data1 Data2 ....
>> Customer Month2 Data1 Data2....
>> ...... ...... ..... ......
>>
>> I am creating a pivottable & chart to show customer data series, with the
>> months on the X axis. Works well, but when I am displaying a series that
>> does not have data for all months, the absent months are left off the X
>> axis (logical enough: how should Excel chart data that doesn't exist?).
>> What I want is for the X axis to always show all months, regardless of
>> the data i.e. there should be gaps in the data for missing months, as
>> there is when multiple series are shown. Relevent point: I am running
>> all of this from VBA code, including the copying in of a recordset from
>> an Access database.
>>
>> This doesn't seem like it should be such a hard thing to do, but I have
>> researched this every which way, and can't find a good way to do it. The
>> only solutions I have thought of are:
>> 1. Loop through my recordset and add customer & month entries (with
>> missing data) for all missing months (Ugh).
>> 2. Some kind of voodoo with a dummy series (haven't figured this one out
>> fully though).
>>
>> I would be really, really grateful for any pointers.
>> TIA
>>
>
>