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.
  • [TABLE="class: grid, width: 700"]
    <tbody>[TR]
    [TD]Month Year[/TD]
    [TD]# Complaints[/TD]
    [TD]Option 1:
    Show Items with
    no data" = True
    [/TD]
    [TD]Option 2:
    "Show Items with
    no data" = False[/TD]
    [TD]Preferred
    Solution[/TD]
    [/TR]
    [TR]
    [TD]<June 2016[/TD]
    [TD]100[/TD]
    [TD]Visible[/TD]
    [TD]Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Jan 2016[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]Feb 2016[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]Mar 2016[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]Apr 2016[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]May 2016[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]June 2016[/TD]
    [TD]5[/TD]
    [TD]Visible[/TD]
    [TD]Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]July 2016[/TD]
    [TD]0[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Aug 2016[/TD]
    [TD]0[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Sep 2016[/TD]
    [TD]0[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Oct 2016[/TD]
    [TD]0[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Nov 2016[/TD]
    [TD]1[/TD]
    [TD]Visible[/TD]
    [TD]Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Dec 2016[/TD]
    [TD]1[/TD]
    [TD]Visible[/TD]
    [TD]Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Jan 2017[/TD]
    [TD]0[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Feb 2017[/TD]
    [TD]0[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Mar 2017[/TD]
    [TD]0[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Apr 2017[/TD]
    [TD]0[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]May 2017[/TD]
    [TD]0[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    [TR]
    [TD]Jun 2017[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]Jul 2017[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]Aug 2017[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]Sep 2017[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]Oct 2017[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]Nov 2017[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]Dec 2017[/TD]
    [TD]N/A out of range[/TD]
    [TD]Visible[/TD]
    [TD]Not Visible[/TD]
    [TD]Not Visible[/TD]
    [/TR]
    [TR]
    [TD]>May 2017[/TD]
    [TD]100[/TD]
    [TD]Visible[/TD]
    [TD]Visible[/TD]
    [TD]Visible[/TD]
    [/TR]
    </tbody>[/TABLE]

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?