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.

Tableau table showing a null date
Without changing the date to blank, it shows the word Null

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

Set the alias of a null date in Tableau
Set the alias of the Null date in Tableau as a blank (or anything else)

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
Format a date in Tableau as dd MMM yyyy
Once the field is a Date data type, set the format and 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.

use a Tableau formula to format the date as a string
Build the date in the format you want using date and string functions

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.

Leave a Comment