Month & Week grouping in excel pivot tables with some empty data

2
votes

I am working to group data in excel and I am trying to sort the input dates by month and week. I've found a number of ways to group by weeks (including grouping the date by 7 days and adding a helper column in the pivot table that numbers the weeks), but I'm having a problem viewing months and weeks without data.

For example, in my data set during the month of October, 2 of the weeks have no input. When I try to make the pivot table "show rows with no data" it shows every week in the year, not only the weeks during the month of October.

I would like be able to show every week during the month of October, including those without data, without showing every week in the year. Has anyone encountered a similar issue? I'm on Excel 2016 but my colleague has the same issue on Excel 2013.

Thanks!

Here is a quick screenshot of the pivot table from the data. I am trying to make all of the weeks in the month of October show up regardless of whether or not there is data, but I don't want to see all of the weeks in the date range. I have sample data input from September 16th, 2018 to January 1st, 2019

1
excelpivot-tablepivot-chart
please try something and share to us so that we can tell you what you have done wrong. It is very hard to create the whole solution to your problem. – Rajan Mishra
I have added a screen shot of the pivot table to try and show the problem more clearly. – Matthew

1 Answers

1
votes

Update for anyone looking to do this themselves:

I couldn't figure out a way to do this with Pivot Charts. They lack the ability to group by multiple days as well as by months and years. Instead, I used VBA to select the desired data from the pivot table by checking for the number of the month in each row. (mm/dd/yyyy - mm/dd/yyyy is the format pivot tables use to group days).

I added a dummy row which copied the date ("Date 2") and used that, grouped in 7 days, as the rows for the pivot table. Because I wanted to show items with 0 data I selected that option, which shows every group of 7 days from the first day entered until the last day entered. I used the original Date with Month and Year auto groups to insert Slicers to sort the data.

In VBA you can check which Slicers are selected and use that to determine the range of months you want to display on the inserted chart. This way you can look at one or two months at a time but still show weeks with no data.

While this solution is not particularly elegant, it did allow for a dynamic use of the data that can be adjusted to view specific months or years as was desired. Some useful resources for this method are included below. Again, it is an ugly way to get the desired result but if it works it works!

https://peltiertech.com/referencing-pivot-table-ranges-in-vba/ https://peltiertech.com/create-update-chart-using-partial-pivot-table/