Best practices for Tableau calculation development
As robust as Tableau's default capabilities are, nearly every dashboard development project runs into a point where the data isn't quite right, or the current measures and filters don't quite ask the questions we want the answers to, or the charts aren't lining up exactly as we want them to. Fortunately, Tableau has a robust calculation language, giving us a wide variety of tools we can use to shape and manipulate our data. By the end of a development project, many workbooks are littered with dozens of ambiguously or similarly named fields, making further development and maintenance a pain. Over the years, as I've helped people build Tableau dashboards, I've gathered a few tips, tricks, and rules of thumb that help me keep my calculations manageable and maintainable.
Use folders to organize common UI/UX calculations
In Tableau Desktop, the Data pane on the left-hand side is sorted either by Data Source Order or by Field Name. This makes it difficult to group related calculations without giving them similar names. Folders allow you group related calculations (or other fields) together. Exactly how many folders your project needs varies from project to project, and there's an element of taste to it as well. Do you want all your fields to be in a folder of some kind, or just the multi-step calculations? Regardless, there are a few folders that I always tend to use.
Looking for your next career adventure?
The Filters folder
Some visualizations require more complex filtering logic than Tableau's built-in filtering provides. A boolean (true/false) expression can be used to create a field used only in filters, where data evaluating to FALSE gets filtered out. It's also easy to chain multiple boolean values together to get a more complex filter.
For example, perhaps you want to show all data from 45 days before and 30 days after a specific date chosen by the user. I might put calculations like these into my Filters folder:
The Formatting folder
Calculations can also be used to control positioning, titles, or other formatting behaviors. Many of these are simple and static, or they’re used to ensure that edits only need to happen once. For example, you can create a calculation containing a single string value, such as “Distribution,” then edit titles, labels, or captions to use that field as a dynamic value. This way, changes only need to be made in this one calculation to populate the change throughout the workbook. You could even make a parameter and let the users choose the value!
Other examples include introducing jitter to a scatter plot or creating clustered bar charts using a dimension. I also like to include calculations here that just contain single functions, like INDEX(), LAST(), or TODAY(), but that’s more personal preference than anything else.
The Aliased Duplicates folder
Occasionally clients want to label the same fields with different names, or different fields with the same name across different visualizations and dashboards. While this isn’t really a best practice, there are instances where it’s appropriate, particularly when using Measure Names and Measure Values. I’ve frequently found it useful to have multiple copies of a given measure or dimension that I can give a unique name, a different set of member aliases, or a different date or number format. However, visually they clutter the data pane, and it’s nice to have a filter to hide these away in.
A note of caution: If you’re duplicating a calculated field by right-clicking and selecting Duplicate, the new field will contain the calculation formula at the time of duplication. This means the calculation has to be maintained in two places, which is a recipe for trouble down the road. Instead, simply create a new calculated field that references the original, like so:
You might want additional folders for Level of Detail Expressions or Table Calculations, or for any logical grouping of fields in your data pane.
One shortcoming of the Folders feature is that there’s currently no way to create subfolders or folder hierarchies; there’s an active feature request you can vote for here.
Have your code speak to the business context
Not every Tableau developer has a background in programming. Many come from business analyst, marketing, or communication roles, and are not very familiar with commenting. In the Tableau calculation language, any text on a line after a double slash (//) are “commented out” and have no effect on the results of the calculation. Commenting can be a valuable tool throughout the project if you make it a part of your workflow.
I always write my one- or two-line comment description when I first create the calculation. If I can’t describe what the calculation is doing in a few lines, then either I don’t clearly understand its intent yet, or the calculation is complex enough to be broken down into more manageable components.
If you’re taking advantage of line breaks and white space for your calculations (and you should), you can also add comments to individual components of the calculation, like this:
In previous versions of Tableau (2020.3 and earlier), a // comments out everything after it on the line, and each new line will require its own //. However, Tableau Desktop 2020.4 and later includes the ability to use block quotes, where you simply define the start and end points with /* and */.
Besides helping you write clear, understandable code, commenting also allows you to test individual components of your calculation by commenting out the rest of the formula. It also lays the groundwork for documentation once the project ends. Finally, comments in the calculation make it easier for the developers who come after to you trust, troubleshoot, and build upon your work, ensuring that your beautiful dashboard doesn’t end up gathering dust on Tableau Server.
Make the complex simple
Large, complex calculated fields can be extremely difficult to understand, troubleshoot, and maintain, so I always recommend breaking complex calculations down into their components. There are three good rules of thumb here:
1. The calculation should be described in a sentence or two.
When your description of a calculation starts to sound like a run-on sentence your third-grade teacher would mark you down over, that’s a hint you may be trying to do too much in a single calculated field. For most (English-speaking) people, code is more difficult to understand than English, so breaking it up into component parts can help future developers follow your calculation logic. You may find you’re using similar underlying components in multiple calculations, allowing you to reduce the duplication of development and maintenance.
2. Replace hardcoding with parameters wherever possible.
Perhaps your calculation assigns a stoplight color based on a percentage KPI—blue if it’s over 90, red if it’s below 65, and yellow otherwise. Instead of writing the threshold values of 0.9 and 0.65 directly into the calculation, create a pair of parameters to hold those values. If those thresholds change in the future, due to change in strategy or reporting methods, the developer won’t have to hunt through the large list of calculations looking for places where 0.9 and 0.65 are used.
3. Never hardcode the same value in more than one place.
If you must hardcode a value, only store that hardcoding in a single calculation and name the field appropriately. If one ever gets updated without the other, that could quietly sabotage the value of your analytics, with the problem going unnoticed for weeks or months.
Give your calculation a name your mother would understand
Writing clear, concise, descriptive names is always a challenge in any development task, but there are a few things to consider when naming your calculations.
First, your names should be human-readable. I would strongly urge against using field names such as “OP_FIN_AggAvgPROFIT_C,” “Calculation1,” “Calculation2,” “Sales,” or “Profit (copy)(copy).” The name should communicate fairly well to a normal human what sort of values they can expect. For example, boolean calculations (true/false) can be given names in the form of a yes/no question, making it easy to understand what to expect when the calculated field is used in a filter or another calculation formula. Between folder organization, field name, and comments, a field should be pretty unambiguous in its usage.
Because the data pane is sorted by field name, prefixes can be used to control sort order or grouping within a folder or the pane as a whole. A natural instinct is to use numeric prefixes to control field sorting, but I’ve found that approach more frustrating than it’s worth for a few reasons. First, development is a process, and you may be combining, splitting, adding, and removing calculation throughout the development process, all of which would require you to rename many calculations to ensure the numeric order has no gaps or duplicates. Second, calculation logic isn’t exactly linear—one calculation may feed into several others at different points in the calculation pipeline. At that point, you start adding 3a) and 4b) and 4c) and then you’re back to confusing, jargon-y field names.
Instead, I tend to prefix a few priority calculations with “*” to jump them to the top of the list. The lower granularity makes the prefixing less distracting and easier to maintain—either it’s a priority field, or it isn’t. I also use a “!” prefix for temporary calculations created to test some behavior or audit the data set, or for any other field I intend to discard before delivering the workbook. Grouping and flagging such fields makes cleanup at the end easier and more complete.
Finally, tags at the end of the field name can help disambiguate similarly named fields. One common tag I use is (Agg), to clearly identify calculations that already define their aggregation method, be it SUM(), AVG(), or some more complex logic.
Calculated fields are wonderful tools for achieving nearly any goal in Tableau, and building some of these good habits into your workflow can make your dashboards easier to develop, easier to maintain, and easier to audit. Remember that the harried developer trying to remember what the heck “Calculation_78514368” was supposed to do might be you!
Looking for your next career adventure?
See our current opportunities
As a Senior Developer at Logic20/20, Jacob Sherman helps clients turn data into actionable insights through useful, fact-based stories. His experience ranges from Shakespeare to SQL to statistical regression, topped off with an end-to-end mastery of Tableau Desktop and visual design best practices.