What better alternative to Tableau

January 11, 2017 | Benjamin Schalk

Tableau tip: visualize non-existent data

Tableau is usually very intelligent at presenting data, following best practice approaches that have been programmed into the normal settings.

This also applies to line diagrams, where the development of a key figure is shown over time. This also means that points in time when there were no entries in the database are skipped and the existing data points are connected with a line. This helps the viewer to understand the development process without being distracted by annoying gaps. These data gaps can often have good reasons, for example if a retail store is regularly closed on Sundays and is not generating any sales. I don't want to confuse the sales development here by showing a break in the sales curve every Sunday.

The case is different if, for example, there is measurement data from a device or machine that is permanently recorded. In this case, the gaps may even be more interesting than the recorded data points, because they indicate technical malfunctions or interruptions in measurement. In this case, I don't want to use the intelligence built into Tableau or even overwrite it so that the non-existent data records are actually visible.

There are two starting positions here. It is easy when the data lines are available even though there are no measured values ​​there. Tableau then does not show the times without measured values, but gives us an indicator that there are empty rows in the data. This indicator can be hidden.

It becomes more difficult if the data is actually not materially available. Then Tableau switches to the default setting that the data points should be connected for legibility. Experienced Tableau users now ask themselves: "But I can go to the formatting menu of the field and set the special values ​​there so that the values ​​should not be displayed?" No, that is not possible. This is because the values ​​simply do not exist, so they are not even special values. So we have to come up with a workaround.

Various settings must now be made here. First of all, we make use of tableaus “Data Densification”, the automatic filling in of data. This works particularly well with date fields. To do this, you can simply go to the context menu of the field in the view and select "Show missing values". This means that the data cells are basically available. Now we just have to tell Tableau how these should be filled. This can only work using table calculations. So we need to create a calculated field that uses a table calculation to insert NULL values ​​into the empty cells. This means that the cells can then actually be treated as existing blank rows.

The calculated field could thus be:

This means that actual NULL values ​​are entered in the lines only available in the view. This field can then be used as a value axis and produces broken lines. The special values ​​(now available in the view) can now also be displayed as desired: as a connecting line, as a zero value, or as a break in the line diagram.

From a visual point of view, however, a better alternative than broken line diagrams are area diagrams. Due to the filled areas, the points in time without measurement data are highlighted much more clearly than with a simple line diagram. You also do not need a calculated field, rather the missing values ​​can simply be displayed here in the date field. However, an area diagram only makes sense for a key figure if you cannot or do not want to use stacked areas.