Display continuous dates in Pivot Chart

Question

I have a set of data in a pivot table with date times and events. I've made a pivot chart with this data, and grouped the data by day and year, then display a count of events for each day. So, my horizontal axis goes from 19 March 2007 to 11 May 2010, and my vertical axis is numeric, going from zero to 140.

For some days, I have zero events. These days don't seem to be shown on the horizontal axis, so 2008 is narrower than 2009.

How do I display a count of zero for days with no events?

I'd like my horizontal axis to be continuous, so that it does not miss any days, and every month ends up taking up the same amount of horizontal space.

(This question is similar to the unanswered question here, but I'd rather not generate a table of all the days in the last x number of years just to get a smooth plot!)

Answer 1

Select an cell in the data series, on the Pivot Table Options tab, click on Setting for this field, click on the second tab, Layout, then check the "Show labels for missing data" option. This seems to do the job - to the extent of including a Feb 29 for every year. Need to investigate that one some more.