Excel 2007 - Pivot Tables - Combination of "Show Items with no data" and "Grouping - Group By Date Range (starting at, ending at)"

[Quirck]

Hi,

first of all I would like to thank everybody involved in this forum answering questions and solving problems.
I have learned a lot from the posts and replies.

I have a problem in excel 2007 with pivot tables.

DATA
The underlying data is a list of items which have been created in the database and the actual date the item was created on.
So basically two fields, date (DD/MM/YYYY) and summary.
At the bottom of this post I listed some demo data

FUNCTIONALITY
I want to present this data in a bar chart, grouped by month, showing for the months where no items where created an empty bar, and to have the posibility to select a date range ...


WHAT HAVE I TRIED
I create the pivot table and set the values to Count of Summary and set the row label to Date.
This creates the pivot table with for every unique date a count of items on this date. Perfect

I want to see the data grouped by month so I right click one of the date fields in the pivot and select "Group ..."
There I select Months & Years and leave the grouping to auto.
This groups the items by month, however months with no items in the underlying data are not displayed.

To display those, I set on the field settings of the Date Row, the option under layout and print : "Show items with no data".
This generates for every month with no underlying items an empty row.

At this moment everything looks ok, but now I want to filter the pivot within a certain date range.
Therefore I select again "Group ..." and change
the "starting at" to eg 01/07/2010 (DD/MM/YYYY format)
& "ending at" field to eg 01/07/2011 (DD/MM/YYYY format)

The pivot table shows a grouping field < 01/07/2010, another field > 01/07/2011, and for 2010 all the months (jan to dec) and for 2011 all the months (jan to dec).

I however would expect that
- jan / feb / mar / apr / may / jun is not shown in the 2010 group
- aug / sep / oct / nov / dec is not shown in the 2011 group.

I have the ability to deselect via a filter the < 01/07201, and also eg 'jan'.
However when I deselect 'jan' via the filter it dissapears from the 2010 group (which is good) but also from the 2011 group (which is not good)

Is this something that is possible with pivot tables?

I control the underlying data as well, so if needed I can change the layout of the datasource.
However a workaround to create for every grouping a dummy empty record in the underlying data is not an option as this would generate over 1 million records.


Demo data

Date Summary
1/01/2010 Item 1
1/03/2010 Item 2
1/05/2010 Item 3
2/01/2010 Item 4
5/05/2011 Item 5
9/07/2011 Item 6
10/12/2009 Item 7
10/12/2010 Item 8
10/12/2011 Item 9
21/01/2010 Item 10
3/01/2010 Item 11
4/01/2010 Item 12
8/01/2010 Item 13
14/01/2010 Item 14
22/01/2010 Item 15


Hopefully everything is clear ...
If extra info in needed, do not hesitate to ask ...


Thx in advance,

 

[Svendude]

I have exactly the same problem, and also posted to the forums here. No reply yet

Did you find a solution to this?

 

[kevile]

Bump, having same problem

 

[Svendude]

I had a similar problem (posted here:http://www.mrexcel.com/forum/showthread.php?p=2631094) and, with some help, found a solution.

I did have to create dummy records, one for each month in my date range, so in other words about 12 dummy records. Instead of using Count, I created another column called Counter which just had the value 1 in each row, except for the dummy records which had zeroes, and I used Sum of that column.

To ensure I just had the 12 months I wanted, I created another column in the data table which used a formula to flag each row with a 1 or 0 depending on whether it fell in the date range I wanted, and I used that column as the page filter for the pivot chart.

In the end I got what I wanted, namely a 12 month chart grouped by month and year, which showed just the months I wanted, with zeroes for months with no records.