Filling the gaps – three levels of difficulty

Forums are most fun when a question really makes you think. For today’s blog post, we will be revisiting a question that asked how a table with missing values, i.e. nulls, could be filled not with the preceeding value but with the following value. Also, there can be multiple instances of null values in succession.

For this exercise, however, we will be going through three levels of difficulty to gradually evolve our understanding. Enough talking, let’s get going.

Level 1 – Missing value, fill with previous or following value

So, in this case we have a data source that may have randomly missing measure values but there is always at max one row of missing value like this

In this case, we can easily fill either with the previous value or the following vale by using for the next value

//next value
ifnull(sum([Values]),lookup(sum([Values]),1))

Same works for the previous value by just changing the 1 to -1.

Result

Level 2 – filling with previous value but multiple empty rows

In this case, our data might look like this

Filling the gaps with the succeeding values is – in my opinion – difficulty level 3 so let’s first focus on filling the empty rows with the preceding values. In order to do so, create a calculated field like this

//Lvl 2 - previous value with larger gaps
IFNULL(sum([Values]),
    PREVIOUS_VALUE(lookup(sum([Values]),-1))
)

The previous value formula will repeat itself yielding what we need

If you want to read more on the previous_value formula check out this great and pretty recent blog post on Interworks https://interworks.com/blog/2021/01/07/understanding-previous-value-in-tableau/ by Sebastian Deptalla

Level 3 – filling the gaps with the following value

This is where it gets exciting. Tableau does not offer a forward looking equivalent to the previous_value formula. So we have to work out something on our own.

Let’s say out data looks like this:

and we want for example May 2020 to be filled with the June value July and August shall be filled with September value and so on.

First, let’s create a calculation that simply checks if the month is a month where a null value exists

//next non null index
if not ISNULL(sum([Values])) THEN INDEX() END

Why do we use the index here? Because our follow up formula is using a lookup function which does not allow months/dates to be used.

Ok, so from here on, lets create our lookup for what is actually our next row, i.e. index, where there is no missing value (which means, this is the value we want to use)

//lookup next non null index
WINDOW_MIN(([next non null index]), 1,last())

We start our search not within the row we are at but the next one and go towards the end of the table, i.e. last().

Since every row we will be moving one closer to the last and our “next non null” index is computed table down the window_min function will make sure we always get the next lowest value, like this:

With this being ready to go, we can now conclude the session with looking up the actual value

//lookup_calc
IFNULL(sum([Values]), lookup(sum([Values]), [lookup next non null index]-index()) )

IF our sum of values is not null, then we will just take them. Otherwise, lookup the sum of values at the offset of the lookup for the next non null index less the row we are at.

Let’s use a concrete example:

Here in May we have a null value, so we want to lookup the next value. Same goes for July and August where we have two consecutive null values and October through Febuary 2021 there are several null values for which we want to look up the respective next values.

At the Example of October 2020 we find that our next non null value is in index 15 (which means March 2021 which is the 15th month in our data). So we lookup the value that is at index = 15 less the index we start from, which is 10 for October. So starting from October, we lookup the value that is at 5 rows offset from where we are, which is – magic – March 2021.

Same holds for all other months.

And there we have it. Filling any number of consecutive null values with their successor value, regardless of whether the values increase or decrease.

You can download the file from my tableau public here

https://public.tableau.com/app/profile/steffen2460/viz/fillthegaps-3levelsofdifficulty/Lvl3b

I hope you found that useful and as always, appreciate your feedback.

Until next time

Steffen