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:
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.
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.)
if left([TxSt Semester],4)=”Summ” then “Summer”
else [TxSt Semester]
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)”
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.