Cleaning your filters of Null

by David Sánchez

Do you have a filter with a Boolean that includes Null when shown? Does it annoy you? Want to get rid of it? Well, there are a couple of ways of doing some cleaning, but first of all let’s see why you get those values:

How you write the calculation matters:

  • Do you write a calculation as 1 [something you are comparing] = [reference value]?
  • Or do you write it as 2 IF [something you are comparing] = [reference value] THEN TRUE?
    Are they equivalenting?
  • Maybe you write it as 3 IF [something you are comparing] = [reference value] THEN TRUE ELSE FALSE?
  • Or do you go the full length and type 4 IF [something you are comparing] = [reference value] THEN TRUE ELSEIF [something you are comparing] != [reference value] FALSE ELSE (your choice)?

Well. Quite often, depending on what you are trying to do, all these may accomplish the same. But they are not. Let’s see a quick example:
We have the following data:
underlying data

And we calculate whether the first variable (here Test1) is equal to 1 using the above calculations, we get these results:

Table with comparison of the different booleans

See all those nulls? Guess what happens when you use those calculations as Filters?

Yep.

Selected filters with Null options

Now, before you decide to use one or another calculation, think carefully about the downstream applications you are going to use. And, remember Errors should never pass silently.…but that doesn’t mean that your final user needs to be aware of them!

If you are determined to get rid of them…

Getting rid of the nulls, so some cleaning:

  • Use the calculations 3 or 4.
  • Pick the T¦F dimension of your choosing to create a set. Use that set in the filter shelf, with only your desired options checked (or excluding Null). See a more detailed example of a similar application by Giorgia.
  • Alternatively, Use IIF to catch the Nulls and do with them whatever you want. Catch here is, you cannot use all Booleans so you may need to resort to strings (more on IIF in a later post):
    IIF(([Test1] <= 1),'True','False','False')

Highlighting the Null option in filterHighlight introduced SET as filter and change in options displayed in TEST1 filter

Avatar

David Sánchez

Some Numbers @DS -- Reflection on 4 (fast) months

3 mins read

Fri 26 Jan 2018

Revelations – densification using bins and Index in Tableau. Case in point: normal distribution.

6 mins read

Sun 14 Jan 2018

Tableau for everyone

1 mins read

Tue 02 Jan 2018

Alteryx treating single character strings as boolean - when and how to fix it

1 mins read

Fri 22 Dec 2017