Get a Working Date/Time Format Using a Tableua Web Data Connector

Here’s a tip that resulted from quite a bit of frustration. It has to do with Tableau’s new-ish Web Data Connector and a difference in JavaScript date functions across browsers (particularly, Chrome and Safari). While trying to pull in JIRA data, depending on which JavaScript method I attempted, I would see either a NULL or NaN (Not a Number) in any date field. The frustrating things is that it worked perfectly in the simulator. I only ran into the problem in Tableau Desktop (on a Mac) and could not find any errors in the logs or console.

To start with, Tableau has a pretty good tutorial to help people get started writing a WDC, which I recommend you check out first if you haven’t already. Within their documentation, you can find a cheat sheet for accepted date/time formats. However, even though I referred to this cheat sheet and tried several formats, I could never get anything to produce a result other than NULL or NaN in Tableau. Again, despite the dates rendering properly using the simulator in Chrome. Writing a WDC wasn’t a high priority, so I would bang my head on it for a while, set it aside for something else, and then come back the next week to do the same thing all over again.

A breakthrough finally came when I asked myself, “Does the simulator behave the same in all browsers?” I fired it up in Firefox and saw the same valid results as I saw in Chrome. Next, I brought up the simulator in Safari. Much to my surprise, the dates produced a NULL or NaN! For my purposes, the WDC was connecting to a proxy server, which then connected to JIRA using PHP’s cUrl functions. In order to confirm my suspicion that Tableau was using Safari, I checked the access log for this proxy server. What user agent did I find when trying to connect using Tableau Desktop, rather than the simulator?

Mozilla/5.0+(Macintosh;+Intel+Mac+OS+X)+AppleWebKit/538.1+(KHTML,+like+Gecko)+Qt/5.4.1+Safari/538.1

As you can see, Tableau was using Safari. A Google search lead me to this question on Stack Overflow, which made me think Safari was not quite comfortable with the date/time string provided by JIRA (e.g.:2016-07-23T05:31:15.000-0400). The accepted answer suggests trying out moment.js, which I did and found it incredibly easy to use. Just remember to include the moment.js file when you add your WDC to the server.

With moment.js in hand, the solution was simple. I included moment.js in my WDC with something like the following:


<script src="moment.js"></script>

Then, I used the following code to get the date string supplied by JIRA into a format supported by Tableau:


var dateFormat = "Y-MM-DD HH:mm:ss";

var createdDate = moment(issues[ii].fields.created).format(dateFormat);

Tableau magically rendered a real date! So, if you find yourself trying to solve the same puzzle, give moment.js a try and see if that doesn’t get you onto the next hurdle (which is getting Tableau Server to a version that is compatible with the WDC found in the tutorial/simulator).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s