From time to time I browse through specialized forums, and recently I came across the same issue in two threads from different angles. Basically the question posed is, “how can we display missing values in a line chart?” This question comes into play when we consider what to display if we have a gap.

Missing data may occur for a number of reasons but for now let’s concentrate on the visualization. I will sketch a couple of solutions using Xcelsius, but the general idea of these options applies to other tools as well.

First let’s take a look at the possible initial situation: if we don’t have data for one or more periods in the database.

The user might not even realize it, but something is wrong with the above chart. The 3rd, 4th and 5th months are missing. The reason behind this may be that sales dropped to zero, or that sales for these months are not appearing in the database for whatever reason.

The two options below highlight that something went wrong with the sales (data) during the spring period (months 3-5).

Even though the last chart shows the actual situation in the database, the business users may expect to see a complete chart. If we know that sales did not drop to zero, but in fact records were lost for these three months, then the requirement is to connect the gap somehow.

Calculating an average or weighted average from the two closest values of non-null periods could be the solution. The weighted average is slightly more complex but in the case of a bigger gap, it will result in a linear connection between to two ends, as opposed to a flat line.

When interpolating values that we don’t have (and when we don’t know exactly what needs to be highlighted), I would suggest displaying the gap with a significantly lighter color than the normal, representing that these are calculated values.

Although actual values might differ significantly, in my opinion with proper legends, this representation might be better than breaking the line.

I would be interested to know if others have come across this issue and what you have done. Feel free to leave a comment with your opinion.