Pivot and group dates - Force to show all groupings?

[timberguy]

Hi everyone, in quite a few instances we are generating data based upon events that occur in our system. For instance I may have something like this:

Raw Data.png

In this case we have when something occurred, and in this simple example we just care that something occurred, no summing or other functions we just want to know how many things occurred in each month.

So we create our pivot table and group by dates to find out how many incidents we have per month

Grouping.png

Great so now we have our pivot table, but there is no grouping shown for July because there were no incidents during July.

Pivot Table.png

Now when we try to display this data the chart generated is deceiving. It looks like a fairly flat trend, where it should dip to zero in July.

Pivot Chart.png

My issue is that I don't have a way to force the pivot table or the pivot table to display the months/years with no events to make the X axis representative. Maybe I can go back and insert bogus data (maybe hard with doing a count in the pivot) but that's a lot of manual manipulation that most people aren't going to do.

I read about modifying the "Axis Type:" but when you've done the grouping in the pivot to get it by month, the "Date Axis" is not available as a selection.

Does anyone have a magic solution for doing this?

[humdingaling]

you can insert bogus data in but leave the desc blank so nothing counts

then in source data>hidden & empty cells
select zero instead of Gaps

[timberguy]

Yeah that would definitely be tedious for large sets of data, and this is something my users run into all the time, and I see a lot of charts that are misleading because the X-Axis skips months or years so none of the trends are right unless someone really jumps through some hoops.

Was hoping of a way to manipulate the pivot table or the chart to include groups even if there wasn't data. Seems like it would be very handy to do.

[timberguy]

A-ha!

Figured it out. In the pivot table right click on the grouped date in the rows, and go to field settings. In field settings to to Print & Layout tab, and there is a box for "Show items with no data"

Check this and you'll get the blank months showing up. It adds in some stuff for the edges (beyond the edge of the year, on either side for example)

But you can throw a slicer in on the year and clean that up quickly.

Works exactly how I wanted it to!