Replace a Null Date with Blank in Tableau

A common request when using Tableau for reporting, is to replace null dates with a blank or hyphen/dash. This article shows two ways to change a null date to a blank.

Not always is it appropriate to remove null values, sometimes they are better altered.

Changing a null date to a blank is something that seems like it should be simple; but it’s not.

Both of the methods in this article convert the date to a string, one by changing data type, the other by using a formula. One of the methods finishes with the date as a Date data type, the other as text.

Firstly, assume the expected date format is dd MMM yyyy; for example 10 Jan 2020. It’s a simple date format with a Date data type. But it is more complex when converting the date to string. Therefore, the preference is to have the final data type as a Date.

Only one of these methods finishes with a Date as the data type. That method is a “hack” and probably isn’t expected Tableau behaviour…but it works (for now).

Replace Null Date with blank using the Alias

This method comes from Interworks, who have provided a thorough write up on their site. The steps are simple:

1. Change the data type of the Date to a String (right-click on the field in the list of dimensions and Change Data Type)
2. Right-click on the dimension again (once it’s a string) and select Aliases
3. Set the Alias value for Null to be a space if you want it blank (or a “-“/dash/hyphen/minus/etc)
4. Change the data type back to be a Date
5. Set the format of the date field and see the blank date remains blank

Replace Null Date using a calculated field

This is a more standard way to replace the null date. It’s more fiddly than the Alias method; but it’s not a hack, so will continue to work through the different releases of Tableau.

With this method, use the ISNULL function to identify the not Null dates, and extract the different date parts using date functions. Then build the date into the correct format, as a string.

Firstly, to build a date format of “dd MMM yyyy” as a string, use the following date functions:

The day is simple, use the DAY function and convert to a string using STR():

STR(DAY([DateField]))

The month is more fiddly. Use DATENAME to extract the name of the month. Then keep the left 3 characters:

LEFT(DATENAME('month',[DateField]),3)

Extracting the year as a string is also simple:

STR(YEAR([DateField]))

With the different date parts as strings, build the full formula:

IF NOT ISNULL([DateField]) THEN STR(DAY([DateField])) +" "+ LEFT(DATENAME('month',[DateField]),3) + " " + STR(YEAR([DateField])) ELSE "" END

This formula checks if the date field is not null. If it is not null, then extract the parts of the date to build the date in the desired format. If the date is null, set as a zero-length string, a blank.

The downside to this method is 2-fold:

1. Sorting: it’s not a date so doesn’t sort as a date
2. Format: changing the format of the date is fiddly – it means editing the formula and re-building to a different date format

Hence the Alias solution is preferable, but it may not work forever.