This post uses population data for Washington State population provided by the Washington State Office of Financial Management (OFM). The takeaway hack from this post is how to get two measures of data into one Tableau worksheet while making it look like just one measure. The first measure is historical population, the second is projected population.

It wasn’t easy and took some work to figure out. First, the source Excel worksheet isn’t something that makes sense ingesting into Tableau. The population data is in columns in the source data but for Tableau to ingest in a way that makes the data usable for human analyst, I transposed the matrix; each column of year data was turned into a row of year data. I used R for this data munge.

The key to get the two measures to look as one is to create a second measure, projected population. It’s a copy of the total population for all of the years. I copied the total and called it projected population. In this data, historical data is through 2010. I renamed the original total to historical population and recoded the data for each year after the end of the historical population, 2010, with NULL. Nifty.

The dark vs. light blue differentiates the projected population from historical totals:

Dashes are used to show projections because the visual cue of a dash tells us there is less certainty in the figure. Solid lines are a visual cue for more certainty. When dashes for projections are used, it’s common to see both the solid and dashed lines using the same color. Another way to differentiate historical from a projection is to use color. For a line of the same width, i.e., size, or thickness, a lighter, more transparent shade of the same color is a cue for uncertainty. Color is instead of a dashed line works. In Tableau, a line mark can only be a solid line, not both solid and dashed. So, we use color.

You might notice that there is a gap between the end of the historical population line and the start of the projected population line. Before I added my custom tooltip labels, the transition was a blend. I think the gap is better than the blend and was happy with what Tableau did once I added my custom tooltip labels to my Marks card.

The dashboard is a usual size for something of its kind displayed on the web or as a component on another dashboard. What you can’t see unless I point it out is that the blue color graduates from dark to lighter blue as the population figures decrease in 2010 to 1970. It’s not noticeable when published.

I think the approach and published solution work well. One way I’d like to expand this solution is to use data that has a band or confidence interval for the projection.

The Port of Seattle hosted the Seattle Tableau User Group meeting on May 29, 2013. The meeting was held at the Conference Center at Sea-Tac Airport. Nice facility and great content.

The agenda consisted of two, entirely different activities, including a hands-on analysis of the aircraft delay data set for arriving flights. The meeting started with a presentation.

The Presentation

Michael Drollinger, manager of Aviation Planning and Business Intelligence for the Port, lead the presentation. He and his group totally nailed the Tableau use case and getting IT on board. They also totally nailed the implementation. It’s the kind of success story that deserves to be content at a regional or national user conference for Tableau or other BI event that specializes in operational BI.

The Port realized early on that Tableau is not the be-all, end-all, that it is distinguished as a BI tool that excels when working with populations. If you want to do stats or work with sample data, use something else. They use SPSS to analyze their data then import the results to Tableau for presentation and dissemination. It’s real smart to leverage the sharing capability of the server to disseminate survey findings to managers.

Aside from this being such a refreshing success story, there were other takeaways from the meeting. For one, I had never seen a Likert scale implemented on Tableau before. Nice. The two dashboards I remember seeing looked pretty good. I’m hoping they can share an example or two on Tableau Public.

Regarding the workbooks they publish to their Tableau Server, they use a context worksheet that serves as a kind of cover page. They use it to include meta information about the workbook such as the version number, data sources ingested, and contact information. It’s a nice way give structure and governance to what they publish without dampening enthusiasm about either Tableau or the expansion of their BI program.

I’m not going to cover any of the use cases they discussed. Suffix it to say that they have lots of operational BI opportunities and are taking as full advantage of Tableau as any other implementation I know of. Kudos!

Analysis of aircraft delay data set

During the second half of the meeting, we broke into clusters for some hands-on, BI analyst time. The organizer of the meeting did a great job of having USB drives ready with the Sea-Tac relevant data from the aircraft delay data set mentioned above. The activity required us to derive a business insight from the data. We used Tableau Desktop that was installed on our own computers.

The cluster I worked in wanted to determine the carriers that had a disproportionately high share of delays compared to the carrier’s share of arriving flights. We didn’t come close to finishing within the allotted time. After the meeting, I wrapped up the first part of the analysis, the part that involves identifying the carriers and their delays. Here is our start, polished a bit in Tableau 8:

The unit of measure in the chart is minutes rather than the number of delays.

The next step in the analysis is to calculate the percentage share of Sea-Tac Airport’s flight delays that each carrier is responsible for. This will normalize the data. Navigating through the years and months in the chart, Alaska Airline’s delays look abysmal. But, Sea-Tac is their hub and they might actually have fewer delays per unit of analysis than other airlines.

A component of the Sea-Tac Airport division’s mission is to be in the top-15 airports worldwide for customer service within a couple of years. If there is a problem with carrier delays that the Port can assist in resolving, delays will go down, customer service will go up, and they’ll be in alignment with their mission. That’s how it works.

I plan on finishing the analysis. In the meantime, the chart of delay volume by type of delay and carrier is available on my Tableau Public portal.