Category Archives: Tableau

How do I replicate Excel in Tableau?

The short answer is don’t do it. Tableau is not a spreadsheet program, so if you are trying to make it into one you will often find yourself beating your head against a wall in frustration. At the same time, Excel is not a data visualization tool even though people often try to make it into one because it’s the tool they have on hand and it can generate a wide variety of charts and graphs. Having said that, both tools can do some of the same things so all is not lost. Now for the longer answer…

Often when I hear that someone is trying to replicate an Excel spreadsheet as a data visualization in Tableau it is because someone higher up wants it to be done. They are comfortable with the Excel spreadhseet which has worked in the past and don’t want to change to something new and different. In these situations, you often have to do the best you can to make Tableau work. There is some benefit for consistency and familiarity for end users which gives some merit to doing this.

And actually, in the case of simple numeric tables with limited formatting, replicating them in Tableau is a good way to get started. In my current position we have been using pivot tables in Excel for several years. We knew the data being shown was accurate (or at least it was as accurate as we could get). So, when we first began the migration to Tableau recreating the tables was a good starting point for us. If we could create a table that “looked” the same as the pivot table and the values matched then we knew things were working correctly in terms of the structure and calculations. From there we could begin to further explore options to visualize the data. Tableau made it easy to switch between different types of charts and graphs. We could move things around to look at things in new ways to figure out the best way to show what we felt was important in the data.

When trying to replicate Excel in Tableau, start with getting the numbers to match. Sometimes this means that you need to restructure the data to get it into a format better suited for Tableau. The data interpreter in Tableau 9 and above can help with this. You need to have one row for each combination of dimensions and separate columns for each of your measures. You will likely need to play around with calculated fields in Tableau to work out issues where you’ve been using IF statements in Excel, but it can be done. Since how to do that will vary from case to case, I’m not going to go into it here.

Once you have the numbers matching, you can then begin to work on formatting. This is often the trickier part because of ways that conditional formatting of cells can be done in Excel. In most examples I’ve seen, the conditional formatting is used to highlight or color code certain values that are significantly different from others in the table.

Depending on the type of formatting needed, using highlight tables will allow you to shade the “cells” for different values. By editing the colors (and using advanced options) you can often get close to what you might be looking to do.

Another tool that can come in handy are table calculations. One way to get started with table calculation is to use the built in quick table calculations that Tableau provides (e.g. Difference). Using the “Edit Table Calculations” get it as close to what you are after as you can by adjusting the direction (across or down), relative to (first, last, previous), etc. Now, drag the pill with the calculation from the Marks card to measures. It will create a new calculated field (e.g. Calculation1) which you can open to see the formula being used. Common functions you will see in the calculation are: ZN() which replaces null values with zeros; LOOKUP() which looks up a value or expression based on an offset; FIRST(), LAST(), and PREVIOUS() which are easy ways to reference other values in the table.

Other functions worth knowing about are WINDOW_ calculations. For example WINDOW_SUM can be used to sum across elements that are displayed which might not match how they appear in the underlying data. One example I’ve seen with this is counting the number of distinct cities. If you create a table with state and city you will notice that some city names show up in multiple states. If you do a total of count distinct, you get the number of unique city names regardless of state. If you do window_sum on the count distinct then it takes state into account.

Level of Detail (LOD) calculations can also be helpful to create values that might not be readily available in the table or underlying data. For example, in working with retention data I create a variable that represents the first semester a student shows up in the database {fixed [student id] : min([semester])}. I can then use this variable to calculate the number of semesters, years, etc. that the student has been at the university. It can also help in assigning the student to a cohort. There are several other things you can do with LOD expressions, so I definitely recommend learning about them.

Once you have a calculation created you can drag them to color (or size) in order to customize the formatting in the table. I know this doesn’t go into all the specifics you might need to accomplish particular tasks, but hopefully it will give you some ideas on how to move forward with what you need to do.

 

Linking to a Filtered Tableau Dashboard

During a presentation I recently gave to department chairs, a question was asked about the ability to link to a dashboard with filters already “set”. More specifically, the chair wanted to be able to link to a viz with our Enrollment data already filtered to show only his department. In my mind, I was thinking this should be possible but I wasn’t sure how to do it. But, know I do and soon you will too.

The trick to doing this is to know the URL of the viz that you are wanting to link to. For my example, I will use our enrollment explorer which is located on Tableau Public at:

https://public.tableau.com/views/TXSTEnrollmentExplorer/SelectStudents

If you didn’t already know, the last part of that URL (SelectStudents) actually references the tab which should be displayed. This gives you the ability to link to different tabs within the same workbook. For example, this link takes you directly to the “Who are they?” tab:

https://public.tableau.com/views/TXSTEnrollmentExplorer/Whoarethey?

So, what about the filtering? Well, if you add a query string to the end of the URL, Tableau will parse it when loading the viz. The general structure of this query string should be:

Filter=Value   or    Filter=Value1,Value2,Value3

So, to link directly to the Who are They? tab and have it filtered to where Department is Psychology, the URL would be:

https://public.tableau.com/views/TXSTEnrollmentExplorer/Whoarethey?Department=Psychology

Once I looked into it, this was actually easier than I thought it would be. You can also do this with the URL action for dashboards and pass user selected values of a filter in which case the query string would look something like:

FilterName=<FilterName>

This KB article from Tableau is worth checking out since it goes more in depth than I have here.

http://kb.tableau.com/articles/knowledgebase/view-filters-url

Hierarchies and Dynamic Views with Parameters

Recently a co-worker was asking me about dates and how to handle semesters. What he wanted was the ability to expand a year to show semesters in the same way that years can be expanded into quarters. Basically this was a question of creating a custom hierarchy, and I thought it’d make a good how-to that post for here as well.

For this example, I’m using the “Hierarchies and Parameters” workbook I have posted on Tableau Public at:

https://public.tableau.com/views/hierarchiesandparameters/WkShtHierarchies?:embed=y&:display_count=yes&:showTabs=y

Our first goal is to identify which dimensions we want to use to create the hierarchy. In many cases these dimensions might already exist in your data, but for purposes of this example I actually created two calculated fields based on existing dimensions.

The first is Fiscal Year which is just the year prefixed with FY so it’s more apparent we are looking as fiscal rather than calendar years.

Fiscal Year:

“FY”+str([TxSt FYear])

The second calculated field combines the semesters of Summer I and Summer II into Summer for consistency with how we are now reporting the data. (In the past we reported summer terms separately.)

Fiscal Semester:

if left([TxSt Semester],4)=”Summ” then “Summer”
else [TxSt Semester]
end

Luckily alphabetical order works for sorting the semester, but if not we could change its default sort order to get things appear correctly.

The final step (and really all that is required to create a hierarchy) is to drag [Fiscal Semester] onto [Fiscal Year]. I then did the same thing with [Course College] and [Department].

Now for the catch…

One thing to remember is that option to expand/drill down in the hierarchies will not always be obvious to the end user. So, it might work well at design time but we might want another option to get this functionality to end users. So, here’s a way to do something similar using parameters…

The Parameters worksheet shows examples of that “dynamic” functionality if we aren’t able to use the drill down of hierarchies in the final version but want something similar to it. To do this, I created a parameter (Show Departments?) and a calculated field (Department). The [Show Departments?] parameter is a simple Boolean set to Yes or No. The formula for the “Department” measure is:

if [Show Departments?]=TRUE then [Course Dept]
else “(All Depts)”
end

Basically if we’re showing departments then it’s the name of the department otherwise it shows as “(All Depts)”. The same process can then be used for semesters, which I’ve done in the example.

Of course, this approach isn’t perfect either because it “expands” the height (or width) of the table based on which elements are shown. This makes visual layout difficult on a dashboard, and even the “fit to” options might not work depending on how big the table ends up being once everything is expanded.

So, although the approach isn’t perfect, hopefully it gives you some ideas.

-Marc

 

Tableau Licensing and Installation on Multiple Machines

This shouldn’t come as a surprise to anyone who has read the EULA for Tableau (you did read it, right?), but to help some people out I thought I would point to this helpful FAQ on their website about licensing and the number of installations:

http://kb.tableau.com/articles/howto/using-one-license-for-two-desktop-installations

Basically, for each desktop license purchased, an authorized user can install Tableau on 2 machines, a primary and secondary computer. It is assumed that the primary machine is a desktop at work. The secondary machine is either a laptop or home computer.

NOTE: This information is accurate as of the date I’m posting this, but their EULA could change. Be sure to check the current version to find out where things stand. Information on licensing and installation is in section 2.2 as of this post.

 

Setting Defaults in Tableau

Here’s a quick tip/trick to using Tableau that I don’t see mentioned very often: Setting default properties for dimensions and measures.

Recently I was sitting in through some Tableau training at work, and the trainer was talking about setting up custom sort orders by rearranging the levels of a dimension in a table. This is what I typically see people do, and I admit that I usually do it that way myself. But then he pointed out the ability to set a custom order as part of the default properties for the measure so you don’t have to reorder things every time you use it. I vaguely remember seeing this mentioned somewhere before, but I can’t remember where and don’t think it’s mentioned often enough.

So, how do you do this? Well, in a worksheet, right click on the pill for the dimension or measure. Towards the bottom of the menu there should be an option for “Default Properties”. Now, the exact options you can set are going to depend on whether you are working with a dimension or a measure.

For Dimensions, you can set defaults for “Comment”, “Color”, “Shape” and “Sort”. In the realm of higher ed data, the sort can be useful for setting the default order in which Faculty Ranks or Student Classification or Level should appear. These are two examples where the default of alphabetical order is usually not the order you want, so a custom sort is needed. You can also set custom colors here to make sure they remain consistent across multiple sheets and dashboard you might create using that data source.

For Measures, you can set defaults for “Comment”, “Color”, “Number Format”, “Aggregation”, and “Total Using”. For measures, the default Aggregation is something to definitely keep in mind. Tableau likes to default things to SUM(), but there are situations where you might prefer to always show an average or count instead. Likewise, being able to set things to default as a percentage (under number format) can be useful, for example with retention and graduation rates in accountability data.

Again, I admit to not using this option very often, but it’s something that can definitely save you some time and possible frustration later down the road.

Tip within a tip: One thing that I did bite my tongue on during the training was a comment about the color of the “pills” in Tableau. In general, dimensions are blue and measures are green. However, the color is actually for distinguishing between discrete (blue) and continuous (green) values within either a dimension or a measure. This distinction determines how Tableau treats the variable when you move it onto a sheet. I’ll probably post more about this (with a link to the helpful Tableau help page) since sometimes getting Tableau to do what you want requires converting from continuous to discrete (or perhaps the other way around).

THECB Data in a (more) Usable Format

If you’ve read through some of my previous posts, you know that I tend to use Accountability data from THECB for several of the things I try in Tableau. One challenge has always been the way data is formatted by the Accountability system. It outputs data with one row per institution and separate columns for each measure and year. And of course, sub-levels of each variable are part of the measure names. Needless to say this format doesn’t work very well when trying to use the data in Tableau, or pretty much any program.

My early way of dealing with this was to simply pivot the columns into rows. This created a somewhat usable version of the data with fields for institution, year, name of the measure, and the value. Of course this came with it’s own set of problems. It was possible to compare various institutions on the same measure, but it never worked as well as I would like. Also, it was easy to accidentally include values from multiple measures in an analysis because all values were stored in the same field and you had to use filters to narrow things down to the specific values you were after.

Over Spring Break (week before last), I decided to sit down with my Excel macro and try a new approach to reformatting the data. Long story short, I was able to create a format for the Accountability data that, so far, has been significantly more user-friendly. I’ll be making use of this reformatted data in some of the Tableau examples I’ll be posting over the next couple of weeks. For those that might find it useful here’s the Excel file:

Reformatted Accountability Data (~45 MB)

Now, I should mention that this data is just for public universities and not all institutions in the state. I haven’t spent a lot of time verifying all of the data, so if you need “official” data, you should probably use the Accountability System, or at the very least double check the values you get from this spreadsheet against it. The first few columns include the name of the institution, FICE code, period/year, category, and sub-category. After that are the primary measures from each area of the Accountability system. If time permits, I might get around to providing better documentation for it, but hopefully it’ll make sense to you if you’ve used the Accountability system before.

Showing Change with a Scatterplot in Tableau

Recently the VizWiz blog had a post showing how to create a 45-degree reference/trend line in a scatterplot. The original purpose was comparing countries based on literacy rates for females and males. Those above the line were countries where females had higher literacy rates and below were countries where males had the higher literacy rate. From there the post went on to talk about change in a couple of other datasets. But, there was an issue in producing the correct color coding to represent the change from year to year in those other datasets. This got me thinking that I wanted to see if I could figure out why the color coding of change wasn’t working as expected in those examples. (NOTE: Description of how to get the 45-degree line and fix the issue he encountered are at the end of this post.) Of course, trying to tie things to higher education, I decided to use my standby of accountability data from the Texas Higher Education Coordinating Board (THECB) which I’ve used previously.
Continue reading Showing Change with a Scatterplot in Tableau

Using a Custom Map for Enrollment

The following is a quick demonstration of a way that a custom map can be used to display course enrollment by building for a selected day and time on a college campus. Because the campus in question is rather wide, it created a situation where the visualization had to be wider than I would normally recommend. The most difficult part of this process is actually creating a table that contains the coordinates of each of the buildings on campus. The visualizaton appears after the break…

Continue reading Using a Custom Map for Enrollment

THECB Accountability Comparisons

As I mentioned in a previous post, I spend a fair amount of time working with data in the Accountability System produced by the Texas Higher Education Coordinating Board (THECB). One of the things I always found missing in this tool is a way to compare one institution to another or to a set of other institutions. For example, at Texas State we often compare ourselves to the other emerging research universities (ERUs) in the state. Although the accountability system allows us to pull the data on the institutions, we then have to reformat the data and make comparisons manually. So, in an attempt to simplify this process I put together the following Tableau visualization of the data. It doesn’t include all of the measures from the accountability system (being able to download all the data is another of its limitations), but I did include many of the variables that are commonly used with a focus on undergraduates.
Continue reading THECB Accountability Comparisons