A Dating Lesson: Using Math to Get Dates (by Converting Julian Dates)

I recently had to convert some date fields formatted as Julian dates into more usable Tableau date data types (it’s not a date format that Tableau natively understands) and my web search got me a little frustrated because all the solutions I found were long formulas using string manipulation functions like LEFT(), RIGHT(), etc. That kind of solution can get the job done, but there’s something to be left desired:

String manipulation is sloooooooooooooooooooooooooooooowwww.

How slow? How about one thousand times slower (or more) than using math functions! I like my calculations to be efficient from the beginning: The practical reason is that I don’t have to spend time later going back to do performance tuning. Computers were built to do math really really fast so whenever possible I’ll use math to do my date conversions. The aesthetic reason is that there’s an elegance to creating equations that use the least number of steps, and the play reason is because math is fun!

Therefore in this post I’ll describe a faster, more elegant, and more fun way (IMO) of converting Julian dates into Tableau dates. Note that in some cases you might be able to do the conversion to a date data type inside your database, that could be even faster than the formulas I describe here.

What is a Julian date, anyways?

There are two formats of Julian dates that I’ve run into:

  • The JDE (for JD Edwards) or CYYDDD format where the DDD is the day of the year (a number from 1 to 366), YY is the two digit year, and C is the century where 0 is 1900, 1 is 2000, and so on. For example 5001 is 1905-01-01 and 117365 is 2017-12-31.
  • The “modern Julian date” or YYYYDDD format where DDD is again the day of year and YYYY is the four digit year, so 2017365 would be 2017-12-31.

Now the Julian dates might be stored in raw data as strings or as numbers, either way we’re going to work with them as numbers. So if you’re starting out with Julian dates as strings then the first step is to right-click on those fields and change the data type to Number (Whole).

Learning Three Math & Date Techniques

We can convert Julian JDE/CYYDDD dates in a single formula:

DATE(DATEADD('day',[Julian JDE/CYYDDD] % 1000 - 1,
    DATEADD('year',INT([Julian JDE/CYYDDD]/1000),#1900-01-01#)))

There are three techniques at work in this formula:

  1. Extract the right-most N digits from a number using the modulo operator, for example to get the DDD value.
  2. Remove the right-most N digits from a number to extract the left-most M digits from a number using the INT() function, for example to get the CYY value.
  3. Using DATEADD() to add intervals to a starting date and nest for each date_part as necessary.

Extracting the right-most N digits from a number

The slow way to get the right-most N digits from a number would be something like INT(RIGHT(STR([number]),N)). We can completely avoid the string conversion and need for RIGHT() by using some math.

The modulo operator % is a math function that returns the remainder for a whole number. So 5 % 2 returns 1, 17 % 10 returns 7, and so on. We can use this to return the right-most N digits from a number without needing to do any string manipulation by using [number] % 10^N.

In our case [Julian JDE/CYYDDD] % 1000 will return the last 3 digits of the field corresponding to the DDD aka day of year.

A couple of other uses of the modulo function are jittering marks in a dot plot and creating groups/bins based on the right-most digits when numbers are categorical variables.

Remove the right-most N digits from a number to extract the left-most M digits from the number

The starting way to get the left-most M digits from a number would be something like INT(LEFT(STR([number]),M)). However this gets more complicated with the JDE/CYYDDD format because depending on the date then we might want the 2 left most digits (for years in the 1900s) or the 3 left most digits (for years in the 2000s) which would lead to even more string manipulation to get the desired result, for example here’s a formula I found online for getting the year: IF LEN([Julian JDE/CYYDDD]) = 6 AND LEFT([Julian JDE/CYYDDD], 1) = '1' THEN '20' + MID([Julian JDE/CYYDDD], 2, 2) ELSEIF LEN([Julian JDE/CYYDDD]) = 5 THEN '19' + MID([Julian JDE/CYYDDD], 1, 2) END. All those LEN(), LEFT(), and MID() calls will be slooooww.

Thinking about this from a math standpoint what we want to do is with the JDE/CYYDDD format is to remove the DDD (the right-most N digits) and extract what’s left. The way we can do that is in two steps: 1) turn the DDD portion into a decimal so CYYDDD becomes CYY.DDD, then 2) truncate the DDD decimal portion so we’re just left with the CYY.

We can do that with the formula INT([number] / 10^N). The [number] / 10^N divides the number by the number of digits we want to remove, so 5001 becomes 5.001, 117365 becomes 117.365, and so on. Then the INT() around that truncates the decimal places and leaves us with the remaining whole number.

In our case INT([Julian JDE/CYYDDD] / 1000) removes the right-most three digits so what we’re left with is the century and number of years.

I regularly use this technique to build my own bins, for example INT([Sales] / 100) * 100 creates bins of 0, 100, 200, and so on.

Using DATEADD() to add a number of intervals to a starting date

Tableau’s DATEADD() function takes three arguments:

  • date_part: a value like ‘day’, ‘month’, ‘hour’, etc.
  • interval: a whole number of intervals
  • date: a starting date

The advantage of using DATEADD() over something like DATE([lots of string manipulation to build a string of YYYY-MM-DD or MM/DD/YYYY or DD/MM/YYYY format]) is again that we’re avoiding all that string manipulation and just doing date math, which is really math.

With the first two techniques we’ve converted the JDE/CYYDDD format to into the intervals for the day date_part and the intervals for the year date_part, so now we can use DATEADD() once for each date_part in a nested fashion. All we need is a starting date, and in this case we can take advantage of the CYY structure to figure out the appropriate starting date. This is the number of years since 1900, so to generate the year we can use:

DATEADD('year',INT([Julian JDE/CYYDDD]/1000),#1900-01-01#)

Then to add the DDD day of year we can use DATEADD() with a starting date of the year date we just made. We do need to subtract 1 from the DDD day of year because the first day of the year is 1, not 0.

DATEADD('day',[Julian JDE/CYYDDD] % 1000 - 1,
    DATEADD('year',INT([Julian JDE/CYYDDD]/1000),#1900-01-01#))

Julian JDE/CYYDDD Conversion

Tableau’s DATEADD() always returns a datetime data type, so using the above formula and wrapping the calculation in a final DATE() gives us the desired date data type:

DATE(DATEADD('day',[Julian JDE/CYYDDD] % 1000 - 1,
    DATEADD('year',INT([Julian JDE/CYYDDD]/1000),#1900-01-01#)))

Here’s a Tableau view showing the DDD and CYY calculations along with the final Converted JDE/CYYYDDD calculation:


Julian YYYYDDD Conversion

The YYYYDDD conversion uses a quite similar formula, with one change:

DATE(DATEADD('day',[Julian YYYYDDD] % 1000 - 1, 
    DATEADD('year',INT([Julian YYYYDDD]/1000)-1900,#1900-01-01#)))

The difference is that the YYYY portion of the calculation is returning the full four digit year so we subtract 1900 from that to get a number of years to add to the 1900-01-01 start date.

Here’s a Tableau view showing the DDD and YYYY calculations along with the final Converted YYYYDDD calculation:


Math is fun. Math is fast. Math is your friend. Use math to get dates. (Date data types, that is).

Here’s a link to the converting julian dates to dates workbook on Tableau Public.


A little plug: If you like this post (and can tolerate the occasional bad pun) and want help in finding dates (in your data) please check out my company, DataBlick. We offer Tableau and Alteryx support, consulting, and training and can help you get the most out of your data!

Get Some Pointers on How to Use Tableau (Or Not) Thursday, April 27 in SF

This Thursday, April 27th I’ll be speaking at the San Francisco/Bay Area Tableau User Group at Mapbox’s brand new headquarters on How (Not) to Use Tableau. The talk is based on my experiences helping users and organizations get the most out of Tableau and has something for everyone – tips for brand-new users, dashboard developers, managers, trainers, Center of Excellence/BICC staff, and more! You’ll even see what this has to do with dataviz:

And maybe even see some of my childhood Lego collection as part of a lesson on data structure..here’s a registration link, I hope to see you there! https://www.eventbrite.com/e/how-not-to-use-tableau-lessons-from-zen-master-jonathan-drummey-tickets-33972395349

Row Level Security Using Tableau 10.0 Cross Data Source Filters by Jamieson Christian

Jonathan here. Serendipity is a wonderful thing! I was briefly on the Tableau community forums last week and found this thread where Jamieson Christian described a new method for user- row-level security in Tableau that I hadn’t seen before, and it’s so awesomely cool I asked him if he could blog about it, and he accepted my invitation! I’ve already implemented this method at a client and it brought a dashboard from unacceptable refresh times to nearly instantaneous. So read on for Jamieson’s description.

Row level security often presents challenges, if your security data is not in the same data source as your main data. In such cases, solutions tend to leverage one of four techniques:

  1. Data prep outside of Tableau – often involves cross products that can make data sets unacceptably large.
  2. Passing parameter-based filter through Tableau’s Javascript API – requires using a portal that is not always feasible for organizations, also won’t work with Tableau Mobile.
  3. Data blending — just about the only in-Tableau option prior to Tableau 10.0.
  4. Cross data source joins — available in Tableau as of Tableau 10.0.

This post introduces a 5th option using a cross data source filter. see below for how to set it up!

Continue reading

Multiple Ways to Multi-Select and Highlight in Tableau

Last year in version 10.0 Tableau introduced the highlighter that lets us quickly highlight marks. It’s got two potential limitations, though:

  1. We can only highlight a single value or all marks meeting a search criteria, not multi-select values.
  2. Tableau’s Highlighter (and Highlight Actions) are hard-coded so we don’t have control over the formatting of the highlighted marks.

We can work around those limits in a few different ways:

  • Using a separate worksheet with Highlight Action(s) enabled with Hover or Select.
  • Using a separate worksheet with Highlight Action(s) enabled, a dual axis, and a duplicated dimension to display different marks. This technique was developed by Rody Zakovich in his post Only Color Marks on Dashboard Highlight .
  • Using a self-union’ed data source and a dual axis to get total control over how the highlighted marks are displayed and have more control over the user interface for choosing the highlighted marks. I think I might have invented this technique, I haven’t seen anything quite like it before. This method lets us build views like this:

Read on for how to build these out and choose the right method for you!

Continue reading

Cross Data Source Joins + Join on Calcs = UNION Almost Anything in Tableau v10.2

Since Tableau v9.0 or so every new release has come with new features that simplify and reduce the amount of data prep I have to do outside of Tableau. Pivot in version 9.0, the first batch of union support in v9.3, support for ad hoc groups in calculations, cross data source joins and filters in v10.0, and more in-database unions and join calculations in v10.2. With the join calculations we can now do unions and cross/cartesian joins within or across almost any data source without needing Custom SQL or linked databases and without waiting for Tableau to implement more union support, read on to learn how!

Here are some use cases for unions across data sources:

  • Union data that is coming from different systems, for example when different subsidiaries of an organization are using different databases but you want a single view of the company.
  • Union actual sales data from transactional systems and budget data that might come from an Excel spreadsheet.
  • Union customer & store/facility data sets so you can draw both on the same map.

This post goes through examples of all three using a combination of text files and superstore, and Rody Zakovich will be doing a post sometime soon on unions and joins with Tableau data extracts. (Did you know you could do cross data source joins to extracts? That capability came with v10.0, and we can have all sorts of fun with that using join calculations!)

Continue reading

#data16 Makes 5: My 2016 Tableau Conference Preview

In about 42 hours I’ll be getting on a plane to Austin, TX for the 2016 Tableau Conference, the highlight of my Tableau year where I get to meet, learn from, and celebrate with 12,000 dataviz geeks and a couple of thousand of their enablers (the lovely and talented Tableau employees who make it all possible). This year will be my fifth Tableau Conference and it hasn’t gotten old, I’m in a new role this year as a consultant and trainer at DataBlick so my focus is a little different. I’m certainly going to learn some more, I’m also networking [plug-alert] hey, did you know that you can hire DataBlick to help you do awesome things with Tableau?[/plug-alert], but most importantly I’m getting the chance to serve the fantastic Tableau community by cheering on some great work, showing off some cool projects and hopefully inspiring others to get involved, and helping other users. Here’s a list of some anticipated highlights of my week:

  • All week
    • Meeting people, making new friends, finally getting to see friends who I’ve never met in person, talking about my favorite piece of software, and whatever seems important.
    • Handing out new business cards! We did a redesign at DataBlick and for our website and business cards we have caricatures of ourselves, here’s my totally Zen’ed out Tableau fanboi pic:
      We’re running a contest for whoever can collect all 5 (or 6!) of them, check it out at link. And we’ve got some other swag that I’m really looking forward to handing out, including some presents for the Tableau devs.
  •  Monday
    • Hackathon: One of my #TCMemories from last year’s conference (pic below) was the Web Data Connector Hackathon, seeing dozens of customer developers building out new connectors was fantastic, I’m excited to see even more development that builds out the Tableau ecosystem.
    • Data+Women Meetup: I’m really happy to support the fantastic work and community-building that has been happening with the Data+Women effort.
    • MakeoverMonday: This runs concurrently with Data+Women so I won’t be there. 🙁 What the Andy’s (Cotgreave and Kriebel) started and what the community has collectively created is amazing! Did you know they just won an InformationIsBeautiful award?
  •  Tuesday
    • Healthcare & Life Sciences Meetup. Andy Dé who leads Tableau’s work in healthcare will be facilitating this, I’m looking forward to meeting other healthcare users there. And as I’m one of the leaders of the Tableau Healthcare User Group I’ll be networking to find speakers for our next meetings!
    • Keynote – Tableau Vision. Like it or not Tableau will be having some amount of a culture shift with the new CEO, I’m looking forward to seeing what they say in public and reading the tea leaves.
    • Tableau Labs (they run all week). I used to work in software development and I love that Tableau gives us the opportunity as users to talk directly with the devs about what’s coming, what we like, and what we need.
    • Data-Driven Alerts Come to Tableau. As one of the developers of VizAlerts I applaud Tableau for continuing to build out native alerting functionality and I’m looking forward to Zac Woodall’s presentation.
    • Supporting Eradicating Malaria in Zambia by 2020. I’ve been a volunteer on the #visualizenomalaria project with the Ministry of Health in Zambia, PATH, and the Tableau Foundation and it’s really inspiring. In fact some of the code I’ve contributed to VizAlerts has been to meet requirements for Zambia. Jeff Bernson, Anya A’Hearn, Allan Walker, and I will be sharing our stories and pointing out ways that you can get involved in this or other projects.
    • Devs on Stage. As much as I can point out the flaws in Tableau the developers know them better than me because they are living in that, and there are many fantastic things too. This is the devs moment to shine and a chance for me to cheer them on and appreciate them for what they’ve built that has changed my life, I’ll be down in front and making a lot of noise.
    • Elections! Besides the general elections the *really* important election for me is at my daughter’s awesome school where she (at the age of 9!) is running for school president, they’ve done a whole election theme where she’s had to write and give speeches, produce media, participate in debates, get interviewed, etc. (Yes, I’m a proud papa). If you see me staring at my phone Tuesday afternoon it’s because I’m updating my Facebook feed for the election results.
  • Wednesday
    • Healthcare User Group. I’m facilitating this lunchtime group where we’ll have another meetup and Nicole Webb and Marina Chakhalyan of Adventist Health will be presenting a great story on a workforce dashboard. I’m told that this user group will fill up, so get there early!
    • Jedi Chart Types. Chris DeMartini and Adam McCann are going to describe how they build their stunningly beautiful network graphs.
    • VizAlerts: KPI-Based Alerting, Burst Emails, and More for Tableau. Matt Coles and I will be  presenting VizAlerts 2.0 and a whole series of demos of use cases for VizAlerts and where it’s applicable (or not). We’ve also got a special audience-interactive demo (and contest!) planned.
    • Use Tableau Like a Sith. Unfortunately this is scheduled at the same time as my VizAlerts session so I can’t be there but I’ll be there in spirit. Plus DataBlick has something special in store for the Sith-lords in training.
    • Data Night Out. I have so many great memories of making new friends here and catching up with old ones, it’s always a highlight. Here’s a shot of a few thousand folks at #data15, plus a few crazy ones heading out on an aerial tour of the party:
  • Thursday
    • Zens in the Community. I’ll be in the Tableau Community area for an hour to meet whoever shows up, I love to hear what people are doing and encourage them to get more involved.
    • Tableau Zen Doctor. One of the responsibilities of being a Tableau Zen Master is that we spend some time in Tableau Doctor helping users, for me helping other users on the Tableau forums, over email, over screenshares, and with HelpMeDataBlick has taught me so much that I’m grateful for the opportunity to do that in person.
    • Bill Nye. The USA only seems to have room for a couple of public scientists at a time, I’m thankful we got to see Neil deGrass Tyson (twice!) and now Bill Nye.

And there’s all the ones that I won’t get to see in person but will be watching on video afterwards, sessions by Doug Grindel and Alan Eldridge and Bethany Lyons and Jock MacKinlay and Maureen Stone and Craig Bloodworth and Jewel Loree and Michael Kovner and Amanda Pype and  Mark Jackson and Matt Francis and Heidi Lam and Andy Kriebel and Melanie Tory and Jeffrey Shaffer and so many more…it’s going to be a great week.

If you want to connect with me during the week, look for me on Twitter @jonathandrummey. Also Keith Helfrich and Chris DeMartini have put together a time-suck of a #data16 Twitter network analysis viz at rhsd.io/tc16-network-viz, it’s totally fascinating! Tweet #data16net to let them know you are looking at it!

See you there!

You Can’t Get There From Here: My Checklist for Connecting to Databases

I grew up with the Maine story “Which Way to Millinocket” that famously ends in “You can’t get there from here.” Sometimes I feel like that when connecting to a brand new database in Tableau, here are a couple of recent errors:



“Unable to connect to ODBC Data Source. Check that the necessary drivers are installed and that the connection properties are valid. Unable to connect to the server […]. Check that the server is running and that you have access privileges to the requested database.”

The message doesn’t indicate what the exact problem is nor how to fix it, just some unhelpful suggestions. It’s the “You can’t get there from here” story.


[IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: “TCP/IP”. Communication API being used: “SOCKETS”. Location where the error was detected: “”. Communication function detecting the error: “selectForConnectTimeout”. Protocol specific error code(s): “0”, “*”, “*”. SQLSTATE=08001

Unable to connect to the server […]. Check that the server is running and that you have access privileges to the requested database.

This one is even worse…there’s a bunch of developer-ese that is most likely leftover code from  back when IBM still shipped Token Ring and other protocols, it has no meaning to us. It’s like the other famous Maine farmer-giving-directions story where the farmer says “Go a few miles or thereabouts and take a left at the corner where the Hanson’s barn used to be that burned down awhile back.” The only marginally useful bit of the error message is the suggestion at the end (that is repeating what’s already at the top of the dialog).

The challenge with error messages like these is that the developers are doing the best they can and interconnecting pieces of software (like Tableau and your database of choice) is hard, sometimes there’s just not much information available to explain why things don’t work. Even more challenging is that all users like us care about is what we need to do next. This post is about what I do next when I run into errors like this. I use a checklist:

Checklist for Database Connection Errors

  1. Double-check that whatever information you were given about the database connection is correct and try again. For example I once was trying (and trying) to connect to a database server named something like “mhlsql” only it was really “mh1sql”. Oops!
  2. Triple-check that your information is correct. For example I was once given a database password that had a lot of gobbledygook and numbers and manually typed in the numbers again and again and had accidentally reversed two digits. If you can copy & paste information into the forms then do that instead of typing.
  3. Check whether you have network connectivity in general. Browsing a random site on the web covers this. I’ve been frustrated thinking I couldn’t connect to a server when in fact the entire building had lost internet access.
  4. Depending on your system and permissions you may need to skip this step: Check whether you can connect to the database server using a local (i.e. on your machine) database querying application like SQL Server Management Studio, MySQL Workbench, TOAD, etc.*** or an existing BI or reporting tool like Cognos, Crystal Reports, etc. This requires:
    1. Having the right database driver. You can check your local driver against the versions at http://www.tableau.com/support/drivers and install a new one if necessary. Also databases are sensitive to 32-bit and 64-bit distinctions so you’ll need to know what is right for your computer *and* the database. This is a place where you may need to ask your IS/IT department for information and/or to install the driver.
    2. Has that database server authorized your IP address? If you’re connecting to a database server for the first time it might be locked down and blocking your IP address. You can do some testing of this in step 5 below on using ping, telnet, etc. but ultimately this may need to go to IS/IT.
    3. Do you have the right username/password or credentials to connect? Again, if you’re connecting to a database server for the first time you might not be set up to connect to that server.
    4. Use the querying application or BI/reporting tool to check whether you can access the particular database as well as table(s)/view(s)/stored procedure(s) that you want to.  For example you might be able to connect to the desired database but not have permissions on certain tables in the database.
    5. Verify that your Tableau data source is using the same settings from steps #3. For example I’ve made a mistake where I accidentally connected to the production database in Tableau (where my credentials were limited) and the test database in the querying tool and then was wondering why I got errors in Tableau.
  5. If you don’t have a querying application or you tried one and couldn’t get that to connect to my source then check whether you can ping or telnet or Remote Desktop/ssh into that database server machine (i.e. know that the machine is up). These aren’t always available depending on the configuration of the server and what kind of permissions you have.
    1. You can run ping from the command line, the command is ping [hostname or IP address]. Getting the hostname or IP address for some databases can be difficult, for example on Windows if you just have a SQL Server database name. If you check your DSN (Windows) you can get the IP address or host name, you’ll need to open up the ODBC Administrator application which Microsoft has moved around in different releases, do a web search for your version. If this doesn’t work it could be that ping has been disabled on the database server machine or that the database server is refusing connections from my machine.
    2. You can also run telnet from the command line, the command is telnet [hostname or IP address] [port]. Again you can sometimes find the port in the DSN, you can also do a web search to find the default port for my database. If this doesn’t work it’s not a sign that the database doesn’t exist because the port could have been mapped to something else or it could be that the database server is refusing connections from your machine.
    3. If you have login credentials on the machine that the database server is running on then Remote Desktop or ssh can be useful to validate that your machine can at least “talk” to the remote machine.
  6. If none of the above works then if you have a co-worker who does have a successful setup try connecting from their machine. On Windows if you’re using saved DSN’s then you’ll also need to check that the DSNs are exactly the same on both machines.
  7. Do a web search and hopefully find something.
  8. Wave the white flag and submit a trouble ticket to IS/IT.

*** In general having a local querying application is useful in debugging performance issues because your can grab a query from the Tableau Performance Recorder or Tableau logs and then run it to see what it does.

If you have any other tips, please submit them in the comments below!

Do you like this post? Would you like to get this kind of help available to you? Check out DataBlick, we offer Tableau and Alteryx consulting, training, and on-demand support.