Recently a client asked me what is the difference between NULL and 0 values in Tableau. The answer to that is rather simple: a NULL means that there is no value, we’re looking at a blank/empty cell, and 0 means the value itself is 0. Considering there is a difference between NULL and 0, the way Tableau treats these two values therefore is different as well. Here below I will show you this in a quick example.
Explaining the difference between NULL or 0:
Imagine you’re selling fruit and want to keep track of how many items of each fruit you’ve sold. You’re quite the boss in the fruit industry so you actually own not one, but three stores. Your overview of sold fruits on a particular day might look something like this:
Ok, so sales weren’t that great, but let’s keep the numbers simple for this example.
You see that in the overview that Store A has two NULL values. This is because Store A doesn’t sell apples and strawberries, so we left those cells empty. We don’t want to add 0 here, because this whole category should be left out of your calculations. You don’t sell it, you don’t want to include it.
Store B and C both do sell apples, but just didn’t sell any that day. Therefore we put a 0 in those cells. Store C is the only store that sells strawberries, but unfortunately didn’t sell any of those either, so also a 0 there.
If you look at this on a sheet in Tableau, this is how the data will be displayed:
You see the NULL values turning up as empty cells, and 0 will show up just as the number 0.
Calculations with NULL values
Now here is where it becomes relevant if you have a NULL value or an 0 value: when you want to do calculations.
If we want to look at our average sales for the day, we only want to take into account the fruits that we are actually selling in that store. So for store A you want your total sales divided by 3, not by 5. For Store B you want your sales divided by 4, and for Store C, who sells every type of fruit, you want the total sales divided by the total number of fruits: 5.
This is exactly what happens with these empty cells! Tableau will only take a category into the calculation if it actually has a value assigned to it. As you can see this will give us different averages for each store:
Keep in mind that all three stores have the exact same amount of sold fruits. Still the numbers for all the stores are different, because:
14 / 3 = 4,667
14 / 4 = 3,500
14 / 5 = 2,800
As you can see it’s important to keep this in mind when working with Tableau, as you will get different results.
Wanna learn more about Tableau or Alteryx? Do you want to use these tools to their full potential? Check out our trainings and consultancy services!