Pivot Table: Ignore specific months of specific years when grouped by month and year

Hello there,

I have a set of entries.
One of the fields is the date (_date).
Another field is the name of a building (_Building): A,B,C, etc.

When the building was active, there is an entry with the date and the name of the building.
When there is no entry for a specicif date and a specific building, it means the building was not active.

I want to plot the number of buildings active per day.
For that I have the Date field on the X axis, and values are "Count of _Building".

This works perfectly.

Now, if I want to see the sum of the days each building was active each month, I group the _Date per Month.
Because my data goes for 18 months (March 2010 to August 2011), I also need to group the _Date per year so I do not "superimpose" the months of different years (I would otherwise for example end up with March 2010 + March 2011).

This works fine.

BUT, I end up with a chart which goes from January 2010 to December 2011.
I would like to have my X axis going from March 2010 to August 2011. (so get rid of Jan 10, Feb 10, Sept 11, Oct 11, Nov 11, and Dec 11)
If I filter/untick some months, it removes the month but for ALL the years.

I could I deal with that ?
Many thanks!

AzmL

PS: Some months do not record any active building, so I used the "Show items with no data" option of the Filed Settings (tab "Layout & Print") of _Date.

Re: Pivot Table: Ignore specific months of specific years when grouped by month and y

Here are some screenshots for more details.

If I just plot the data as described above, I end up with the following:
[ATTACH=CONFIG]57184[/ATTACH]

You can see that the months when no data was recorded are ignored: May 2010 and March 2011 are missing (red circles).

I want to be able to see these empty months.

In order to do so, I went into the 'Field Settings' of my date field, Tab 'Layout & Print' > [x] 'Show items with no data'.
I get the following:
[ATTACH=CONFIG]57185[/ATTACH]

The problem is that now I also have the January and February 2010, and September to December 2011 months, which are out of the range of my analysis.

This is a dummy example.
In reality, I have data covering from December of a year, to Februaryof the year +2.
So For 15 months of data, I have a 36 months large chart.
Not convenient, not easy to read, and confusing because the buildings were active before and after the range of my analysis, it is just that I do not want to display the information (not relevant to my analysis).

Any help is highly appreciated.

Cheers
AzmL

Re: Pivot Table: Ignore specific months of specific years when grouped by month and y

The Excel file I used to produe my dummy Charts is attached.

Re: Pivot Table: Ignore specific months of specific years when grouped by month and y

Hi Herbds7,

Thank you for your help.

This does not work if I have 'Show items with no data' activated.
('Field Settings' of the date field, Tab 'Layout & Print')

[ATTACH=CONFIG]57203[/ATTACH]

The filter works in the sense that data before and after are effectively hidden, but it does not hide the labels on the time axis.

Re: Pivot Table: Ignore specific months of specific years when grouped by month and y

Yes, I aready thought about that, but this will impair my data table.
I wish other options were available.

Thanks for your suggestions anyway!