A Solution to Tableau Line Charts with Missing Data Points

Data

A Solution to Tableau Line Charts with Missing Data Points

by Paul Field

A question that seems to come up every so often as a consultant is around how Tableau doesn’t show data where there is no data. Sounds kinda obvious, doesn’t it? But let’s frame this slightly differently and we can see that whilst this does make sense, it can be a bit of an annoyance.

Let’s take a look at a simple data set showing sales in June 2018:

Simple Sales Dataset

Two things to notice. Firstly, I’m English and live in Australia. Sorry to our U.S. friends, but I can’t bring myself to use your date format without dying inside a little. Secondly, not all dates are populated. We don’t have data against June 9, June 4 and a couple of other dates. So, if we plot this as a line chart in tableau we get:

Line Chart in Tableau

I have added Date to the label for clarity of what is going on. We can see that we have no data points at all for the missing days. This makes sense. Tableau isn’t inventing data, and having no data point is different than having a null value. But this is likely a day where there were possible sales but there happened to be none. In this case, it probably makes sense to consider these days as having $0 in sales, and the line chart should represent this. Looking at the chart above, we’re not seeing an accurate trend for our business.

If we do want to see these dates, even when there is no data, how do we do this?  Of course, we can use some ETL tool to update the source data. But if that isn’t an option, can we make this work in Tableau? Yes! The obvious answer is to use the IFNULL function, and this would work great if our data looked like this:

Missing Data

But our data doesn’t look like this, so the IFNULL function won’t work as there are no nulls in the data. As mentioned above, we don’t have null values, we have no data. This is a critical and often misunderstood point.

The Solution

The trick is to use a table calculation, but of course, we don’t want to change any data values. We’ll build a table calculation that pretty much does nothing other than saying to Tableau, “Hey, I’m a table calculation.” The reason for this is that this turns on data densification (where Tableau will essentially “fill in the gaps” in a date/time series). I have built a calculated field:

Tableau Calculation

The IIF expression will always return 1. Consequently, the result of this expression is the same as ZN(SUM([Sales])). Now if we add our original Sales field and the new calculated field to our worksheet, we see the same thing:

Add Sales and Calc

So, what … it doesn’t work? Sorry, it does – stay with me. There’s just one more step. We need to click on the Date pill and select Show Missing Values:

Show Missing Values

With our new calculated field, we see a nice continuous line as opposed to the stop/start view of the original field:

Continuous Line

So, there we have it – a nice, quick and easy way to fill your data out and get a fuller picture of line charts. Of course, this should be used with caution. Sometimes having no data displayed is the right option. For example, if we were a retail outlet that was only open on weekdays we wouldn’t want our chart dropping to zero every weekend.

More About the Author

Paul Field

Analytics Consultant
A Solution to Tableau Line Charts with Missing Data Points A question that seems to come up every so often as a consultant is around how Tableau doesn’t show data where there is no data. Sounds ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072