Using string calculations to create messages for NULL values

Guiding and ensuring your users interpret the data correctly is the key to a successful data visualisation.

So, question. What happens when your users come across NULL values in your visual, as in the instance below?

2016-08-16_23-51-55.png

Best case, they simply understand that there are no recorded sales for that country during that specific month.

Worst case, they come up with some mind boggling conclusion that the Belgium store was forced to close due to declining sales in September 2015.

But how about we ensure the data is interpreted correctly through creating a combined lookup/conditional/string statement.

2016-08-17_00-12-20.png

Tableau introduced a neat workaround in this Tableau knowledge base article, but there a re limitations. We may need to ensure that we distinguish between these NULL values and true zeros. True zeros may exist because we are rounding, or if you are presenting your data in 1,000s, 1,000,000s etc; they may also exist because you are working with a measure value that can be both positive and negative, such as profit, and in turn summing these values may equate to a true zero value.

The method outlined in this blog is a very simple tweek to that outlined in the tableau article, but instead of returning zero, we return text.

2016-08-17_18-20-14

The lookup(sum([Sales]),0) is effectively evaluating what the value is of that specific cell (because we are offsetting by 0). Where are cells are empty, this with return the value NULL.

So by wrapping this formula with a isnull() we are quite simply returning a TRUE FALSE dependant on whether the vale is infact NULL or not.

By then using an IF statement we can identify what should happens if the condition is TRUE (i.e. it is NULL), where we return the string “No sales”, and where it is FALSE we return the sum of sales.

A note here is that the data type outputs from an if statement must return the same data type, and as a result we must change the datatype of sum([Sales]) calculation and to do this we simply wrap this in an str() – string- function.

Because our output is now a string and not a number, we as a result loose the ability to format the number, as a number (if that makes sense). This is why I include the round() function, but you may also wish to do some further manipulation such as adding a currency indicator, or thousands separators.

2016-08-17_23-32-50.png

So can we use this to label NULL values on bar charts? The answer is yes, but it is not as simple as placing our new string field on the label shelf and the sum([Sales]) on the rows/columns shelf.

2016-08-17_23-48-18

This is because the bars where there are ‘No sales’ don’t actually exist, so there is nothing to label, so we now need to perform a very similar calculation to return a value so there is something to label, I return the value 0, in order to build a mark which we can then label.

2016-08-17_23-47-07

2016-08-17_23-47-54

And now we have a labelled bar chart 🙂

Ben

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s