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