Pivot Table Date "Show items with no data" does not respect grouped date range

[mdiller]

Hi Everyone! First post here. There is SO much good on this site that I've taken, and until now haven't had to ask! Bravo.

Here is the situation:

• I have a data set of complaints with a date field and a product field.
• Some of the products do not get a complaint every month.
• I automatically create a PivotTable/PivotChart of the data using VBA which I use multiple times per month.
• The resulting file is provided to...not so excel savvy coworkers (i.e. they can't be required to do anything but copy paste the data into their presentations)
• Presented data covers a rolling 12 month window.
• Users can filter for their specific product line and complaint category (this means brute force adding rows of "dummy" data to the data set isn't going to work.)
• I'd be happy with a VBA solution if there is one, but would prefer to find some PivotTable setting I'm missing. Currently I have VBA copy and paste the data outside of the PivotTable, but it is VERY clumsy solution.
• I'm using Excel 2010...but may get upgraded to 2013 soon.

And here are my current options (that I know of):

1. "Show items with no data" = False
   • When I show months and year (grouping the date field) the months with no data get skipped.
2. "Show items with no data" = True
   • This ensures that all the months are included, BUT it also includes the months before and after my 12 month window.

Example:

• Here is a made up example of the resulting PivotTable and the current options and solution I am looking for.

  • Month Year	# Complaints	Option 1: Show Items with no data" = True	Option 2: "Show Items with no data" = False	Preferred Solution
    <June 2016	100	Visible	Visible	Visible
    Jan 2016	N/A out of range	Visible	Not Visible	Not Visible
    Feb 2016	N/A out of range	Visible	Not Visible	Not Visible
    Mar 2016	N/A out of range	Visible	Not Visible	Not Visible
    Apr 2016	N/A out of range	Visible	Not Visible	Not Visible
    May 2016	N/A out of range	Visible	Not Visible	Not Visible
    June 2016	5	Visible	Visible	Visible
    July 2016	0	Visible	Not Visible	Visible
    Aug 2016	0	Visible	Not Visible	Visible
    Sep 2016	0	Visible	Not Visible	Visible
    Oct 2016	0	Visible	Not Visible	Visible
    Nov 2016	1	Visible	Visible	Visible
    Dec 2016	1	Visible	Visible	Visible
    Jan 2017	0	Visible	Not Visible	Visible
    Feb 2017	0	Visible	Not Visible	Visible
    Mar 2017	0	Visible	Not Visible	Visible
    Apr 2017	0	Visible	Not Visible	Visible
    May 2017	0	Visible	Not Visible	Visible
    Jun 2017	N/A out of range	Visible	Not Visible	Not Visible
    Jul 2017	N/A out of range	Visible	Not Visible	Not Visible
    Aug 2017	N/A out of range	Visible	Not Visible	Not Visible
    Sep 2017	N/A out of range	Visible	Not Visible	Not Visible
    Oct 2017	N/A out of range	Visible	Not Visible	Not Visible
    Nov 2017	N/A out of range	Visible	Not Visible	Not Visible
    Dec 2017	N/A out of range	Visible	Not Visible	Not Visible
    >May 2017	100	Visible	Visible	Visible

    
    <tbody>
    </tbody>

Any ideas? I've been looking for a solution for a few years now, and have found nothing online. Am I the only one that runs into this problem???

 

[mdiller]

Somehow I missed that first box under "Month Year" (cell A2 if you will). It should say <Jun 2016.

 

[mdiller]

OK, I'm going crazy. In both the original post and the reply it seems like the less than sign removes everything after it. What I wanted to say was that the cell should say "Less than Jun 2016" or "<Jun 2016".

 

[bschiebe]

Good morning,

I am having the same issue, and I have been trying to work through it for about a week with no headway. Any possible solutions out there?