Why are populated Date & Time fields Null on Tableau?

by Ali Agah
These values are clearly not Null as String but return as Null when converted to Date & Time

Tableau is normally clever with Date fields and Date & Time fields but it's not perfect.

I noticed last week that if you input a column with the Date & Time formatted in multiple ways, it sometime cannot convert all of them properly.

For example, when the ISO time field below is a date & time field, all visible values in the data source pane are Null. But when I convert it to a string type, the values return... looking like Date & Time values.

Both dates and times are formatted differently. Note the field is a String data type in this view.

This is a niche problem though, I've actively tried to replicate it but Tableau keeps managing to work it out. My assumption is that the problem is isolated to a few datasets for reasons that I haven't fully worked out. So, if you find another dataset with the same issue, please let me know!

That said, the problem appeared for a number of Tableau Desktops, computers and users (using the same dataset).

Why does this matter?

This practically forces you out of using the field as a Date & Time. You have to change the data type to avoid the Nulls. Alternatively, you keep it as a Date & Time field and just accept that you have Nulls.

I don't like either option...

Date & time fields provide a flexibility that other data types just cannot provide. Most importantly, you cannot use date calculations nor create continuous timelines as easily.

The problem may not even be isolated to only Date & Time fields either!

What's happening? My theory:

Changing the data type of a field inputted from a data source is a way of telling Tableau how to view the field so Tableau can only see it in one way.

If the values in the column are in different formats (e.g. some dates are dd/mm/yyyy but others dd-mm-yyyy) then, sometimes, Tableau cannot view them as the same thing. So, it often recognises one format and returns Null for the rest.

You can see this more clearly below.

As a Date & Time field: Tableau returns Null for anything not originally formatted as yyyy/mm/dd hh:mm:ss

My solution

Theoretically you can use Dateparse() with all the formats in your datasets. But that's laborious and requires you to go through every row. NO THANKS!

Make Tableau do it...

1) Convert the field to a String

2) Duplicate the field

3) Convert the duplicate to a Date & Time field (or the data type relevant to you)

Why does this work?

Duplicating the field creates a calculation (see below).

The duplicated field is not a column but a calculation equal to the original field.

This means Tableau cannot just 'view' the values differently as it did with the original field inputted from the data source.

Therefore, converting the duplicate forces Tableau to write a calculation in order to change the data type (see below). It takes into account all formats and saves you the trouble of writing the Datepartse() calculations.

Calculation auto-generated by Tableau when converting a duplicate's data type

Again, I wasn't about to replicate the problem and test the robustness of the solution. If you found another dataset with this problem; please get in touch on my twitter or email.

Enjoy vizzing!

Fri 07 May 2021

Fri 04 Jun 2021