How to force time series in pivot chart -- force break in line

I have a housing transaction dataset that looks like this:

geography | date | housing type | sq.ft. | sale price | price/sq.ft.

I can make a pivot chart showing the change in, say, price/sq.ft. over time, and filter that by one of the 20 geographies I have. Here's an example of what I can make below:

I've added two more fields (saleyear and salequarter) and calculated them based on the date field. The problem is that not all geographies had a transaction in each quarter. Instead of showing missing data (i.e., a break in the line), the x-axis shortens and throws off the pattern. You can see this in year 1999 in the image above.

TL;DR: How do I make the pivot chart show the date for a missing value and just show a break in the line?

plnnr

Posted 2014-07-01T00:05:12.070

Reputation: 121

Answers

Pivot tables (and pivot charts) only show data that is present in the data source. If you want to make sure that all quarters are showing on the X axis, you must have all quarters present in the source data. They don't have to have values against them.

enter image description here

Use the "Select Data source > Hidden and Empty Cells" settings to control whether the gap is showing or closed by connecting the data points with a line.

enter image description here

teylyn

Posted 2014-07-01T00:05:12.070

Reputation: 19 551

Thank you for taking the time to answer. That answered my question! Unfortunately, I have to go through my rather extensive dataset by hand and manually add the dummy/missing quarters. – plnnr – 2014-07-03T00:08:00.720

You don't need to do that. You can add the empty data values to the end of the source table. It only needs to be somewhere in the data source, not slotted in perfectly. – teylyn – 2014-07-03T02:01:30.600

Aha, so I could just make a full dummy set of years/quarters with blanks and replicate it across all geographies--maybe not what you were getting at, but it would be easier than trying to find out where the missing year/quarters were for each geography. – plnnr – 2014-07-03T22:05:55.787

yes, that's the idea. – teylyn – 2014-07-04T05:05:37.603