Tableau in the office

I’m going to start sharing some things I do with Tableau in my professional life. I’ve done this once with my Time Duration post. Hopefully, “Tableau in the Office” becomes a recurring series and provides ideas/tips/tricks that you can employ in your daily Tableau routine.

This week I was asked by a colleague for some data on their jobs queue. As my division is the content supply chain for Discovery, incoming deliveries must be QC’d to ensure they meet our technical specifications. We call this process, Technical Evaluation or TE. Our media management system tracks these TE jobs with multiple date fields. We have job created date and job completed date. This is a high volume operation so new jobs are created every day as the team completes jobs in the queue.

To illustrate how I solved this, I’ve mocked up data in the following excel file. This file contains three columns: Created Date, Completed Date and Job Number. I randomly generated 1500 dates between January 1, 2014 and October 31, 2014 to populate the Created Date column. Then I populated the Completed Date column by randomly adding 1 to 15 days to the Created Date value.

I connected to my data source using the following custom SQL technique. For excel file, this would be:

SELECT
[Sheet1$].[created date] AS [date],
‘CREATED’ AS [SOURCE],
[Sheet1$].[job_num] AS [job_num]
FROM [Sheet1$]

UNION SELECT
[Sheet1$].[completed date] AS [date],
‘COMPLETED’ AS [SOURCE],
[Sheet1$].[job_num] AS [job_num]
FROM [Sheet1$]

The important thing to note here is that the first select statement labels the Created date as Date and the second labels Completed Date as Date. This way I have both date values in the same column when I bring the data into Tableau. Also, I added a SOURCE field to designate where the data is coming from.

Once in Tableau, I made a bar chart with Stack Marks turned off. Placed SOURCE in the color and size shelf. This is a great way to see how many jobs are being created versus completed for a given date range. I’m filtering out November in the view below.

job dates

This was my take on this problem. Let me know if you have done something similar in a different manner.

Share Button

4 comments

  1. If “Created” is more like a goal, I would use a reference line for it instead of using a bar in a bar. That’s my personal preference though, because I think that makes it easier to understand which measure you’re tracking against.

    Your view works well too, again, it’s just my personal preference.

    1. Thanks for the suggestion! Created really isn’t a goal in this case. But I can play around with it and see if the user prefers a line than a bar in bar.

  2. This is really great! If you also wanted to measure things like total or avg processing time (create to complete) or the number of jobs open at the end of the month, would you process those measures off of this new SQL query or would you use the original date source?

    1. To calculate your scenario I could use either data sources. The new one would require the SOURCE dimension in the calculated field whereas the original data source would be a END – START calculation. It guess it’s just a matter of preference.

Leave a Reply