Save time and stay consistent with a template of calculated fields

When working with Tableau, it is very common for a data analyst to write the same calculated fields over and over again for various workbooks. This repetitive task is not only time-consuming but also increases the risk of writing inconsistent calculations. This can be especially true when working with a team of analysts who are working on collaborating projects. This solution attempts to fill that gap using functionality that is available natively in Tableau and 100% supported—no XML Hacks.

Use Tableau's copy/paste functionality

You can do the following in Tableau:

Copy/paste sheets from one workbook to another (Brings over associated data source)
Copy/paste/duplicate calculated fields in a single data source/workbook
Copy/paste calculated fields from one data source to another in the same workbook
Copy/paste calculated fields from one workbook to another workbook

The last bullet point is key. Since Tableau allows us to copy/paste calculated fields from one workbook to another, we can create a single “calculated field” workbook that contains all the common functions.

How to create a template workbook of calculated fields

First, create a Tableau workbook using very basic data source, like a single record Excel sheet with a date field, a string dimension, and a measure. From there, create every calculated field you commonly use (and any parameters that go with them). Next, go through and group calculations into folders. This allows you to better organize calculations for when you need them later. Finally, go through and add comments to all your calculated fields (additional details to come later). Then save the workbook as "calculations workbook."

How to use your calculations workbook

Now that you have your calculations workbook, the next step is learning how to use it. Every time you create a new workbook, open your calculations workbook.

Once you’ve done this, the next step is deciding which calculations you need for your current requirement. Once you have decided, there are two options.

1. Press CTRL+Click on each calculation you intend to bring over.

2. If you want to bring all calculations in a folder, press SHIFT+click, and then go down to the last calculated field in the folder and click. This will include all calculations in the folder.

3. The next step is to copy them over. Right-click on any calculated field and choose the Copy option.

4. Once you have the calculated fields copied, go back over to your new workbook (I refer to this as the target workbook). Once there, I right-click on the Data pane and choose the Paste option.

This will now bring over all of the calculated fields you copied from the calculations workbook.

What about the red exclamation points?

Now, you may notice something strange about this. There are a lot of calculations with a red exclamation point. Why is this? Well, using this method has one caveat, but luckily it is a very easy fix. In your calculations workbook, all of your date calculations will be referenced to the field names from the data source you used to build the calculations workbook. For example, in mine, many of the data functions point to a field called [Date]:

The problem is there is no [Date] field in my target workbook; there is an [Order Date] field, and that is the one I intend to use. So, to Tableau, these are invalid calculations. How do you fix this? Easy, you can just rename [Order Date] to [Date]. When you are constructing calculated fields in Tableau, Tableau looks for field names. By changing the target field to the name of the field in your Calculations Workbook, Tableau will fix the dependency issue. To rename a field, simply right-click and choose Rename.

After renaming [Order Date] to [Date], Tableau removes all of the dependency errors and the calculations will become valid.

What if you need that field to be called [Order Date]? No problem! Simply rename it back to [Order Date]. When you rename fields in a workbook, Tableau automatically updates all of the calculations that use that field as well.

Now, another thing you might notice is when you copy/paste calculations with parameters, Tableau instinctively knows to bring over those parameters with the calculation.

So now, not only have you saved time from writing the calculated fields, you’ve also saved time from recreating the parameters. Another amazing thing Tableau does when you copy/paste from one workbook to another is bring over the metadata like comments. Now, not only have you saved time from writing calculations and parameters, you've also saved time in documentation. Best part: Your documentation on calculations remains consistent across all workbooks.

5 quick tips on creating your own calculation workbook

1. Choose intuitive names: I like to name my base calculations according to their function. This helps me when I am copying/pasting them into another workbook. I know from the name what the calculated fields are, and can quickly decide whether or not I need them (If you need a more functional name, you can simply rename it in the target workbook). 2. Steer clear of nested calculations: Since we can't create sub-folders in Tableau, it is difficult to keep these in order. You can alleviate this with good naming conventions, but it can cause issues. 3. Write comments: For my comments, I like to add two pieces of information: a brief description of the calculated fields function and the calculations itself. Note: When you change the field names, it does not update the comment. So you want to keep the field names to something consistent like [Date]. This is just to show the construct of the calculated field for quick reference. 4. Know when not to copy/paste calcs: You will probably still have to create your own calculated fields in the target workbook. This really helps save time for commonly-used functions, but there may be times that you are dealing with data/requirements that ask you to create complex calcs (like nested LODs). In situations like these, it is better to create the calcs in the Target Workbook rather than trying to copy/paste them over. 5. Do what works for you: Most importantly, this is YOUR calculations workbook, so create the calcs that work for you (or your team). Try to keep things consistent, but what is right for your situation. I found using this method has saved me a lot of time over the months. And if you are working with a team, it also allows you to have consistent calculations in your workbooks. The best part of this: no XML hacks. This is 100% within the realms of what you are able to do in Tableau.