Excel 2007 - Pivot Tables - Combination "Show Items with no data" / "Grouping - Group By Date Range"

Excel Help for Excel 2007 - Pivot Tables - Combination "show Items With No Data" / "grouping - Group By Date Range" in Presenting and Reporting Data

Excel 2007 - Pivot Tables - Combination "show Items With No Data" / "grouping - Group By Date Range"

Rate this: (2/5 from 2 votes)

	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,
	Posted by Quirck on 26 Jan 2011

Replies - Displaying 1 to 3 of 3

Order Replies By: Most Recent | Chronological | Highest Rated

	Group Start and End Dates and use Custom Filters Rate this: (3/5 from 1 vote) When you select the start and end dates in the Pivot table grouping, make sure that you uncheck the "Auto" boxes to force the specified dates to eliminate the unwanted months.  I tested this fine with you sample data. In order to filter by month and year, create a custom filter on the data field whereby you select Date Filters > Custom Filter > is not between and then choose the begin date and end date for data not to be shown.  This will allow you to filter out a particular month such as in your example.
	Excel Business Forums Administrator
	Posted by Excel Helper on 26 Jan 2011

Excel 2007 - Pivot Tables - Combination "Show Items with no data" / "Grouping - Group By Date Range" Rate this: (2.5/5 from 2 votes) Hi, already thanks for the quick reply. I tested your remarks, but it seems it is not working. The chart still shows the months before and after the date range selection I would think that jan-feb-mrt-apr-may-jun of 2010 is not shown, as I have selected starting at 01/07/2010. I would think that aug-sep-oct-nov-dec of 2011 is not shown, as I have selected ending at 01/07/2011. Now the graph shows the < and > specifc dates, which I can deselect, but that's not sufficient. Thx for trying to help out ...

Posted by Quirck on 26 Jan 2011

	Only one filter allowed Rate this: (3/5 from 1 vote) I see the problem.  As only one filter is allowed on the dates, we can either remove the < and > extremes or eliminate a month, but not both.  The solution is to create separate fields first in the source data for month and year.  This simplifies the filtering and provides more flexibility on custom date selection in the resulting pivot report and chart. To do this, create a new column Month and a new column Year.  Use the formulas =MONTH(date) and =YEAR(date) on the date field and then include these two columns in the pivot report.
	Excel Business Forums Administrator
	Posted by Excel Helper on 27 Jan 2011

Displaying page 1 of 1