Creating and Maintaining Calculated Fields in Tableau : The Lazy Way

This post is coming off a recent tech tip I gave for GovTug (The Government Virtual Tableau User Group) on how to Create and Maintain Tableau Calculated Fields, The Lazy Way.

Hint: The Lazy way is writing them once, and then never writing them again.

Let's being with some background, and how this tip came about. At my last engagement I worked with a multitude of datasources across multiple departments. And while the data was different, and the requirements changed, I was still consistently creating the same Calculated Fields (Mainly Date related functions) over and over. Eventually I got tired of writing the same calcs multiple times a day, so I set out to find a way to make the process more efficient. The first pass at this involved me cracking opening a TWB in a Text Editor (Notepad), finding the Calculated Field Tags in the XML, Copy/Paste them into a Master Text Doc, and then copy/pasting them into a new workbook as I needed. While this method worked, it wasn't that much more efficient, it wasn't pretty, and it wasn't supported by Tableau (It was hacking of XML). Needless to say, this wasn't the solution.

So what's the solution?

Well let's discuss some not so commonly known features of Tableau.

Tableau has Copy Paste Functions

• Copy/Paste Sheets from one workbook to another
• Brings over associated datasource
• Copy/Paste/Duplicate Caclulated Fields in a Single Datasource/Workbook
• Copy/Paste Calculated Fields from One Datasource to another in the same Workbook
• Copy/Paste Calculated Fields from One Workbook to another Workbook

The latter is key here. 

Since we have the ability to Copy/Paste Calculated Fields from One workbook to another, why not just create a workbook that has every Calculated Field we commonly use (And maybe even those we don't)?

Well, that's what I did.

So how does this work?

The way I did this was by creating a Workbook off of a very basic datasource, One record Excel sheet with a Date Field, a String Dimension, and a Measure. From there I created all of the Calculated Fields (Mostly Row Level Calcs) I commonly use (And any parameters that go with them). Next, I go through and group my Calculations into Folders. This allows me to better organize these calculations from when I need them later. Finally, I like to go through and add comments to all my calculated fields (I'll discuss this later). I then saved this Workbook as "Calculations Workbook". 

Now that I have my "Calculations Workbook", the next step is learning how to use it. Everytime I create a new workbook, I also open my "Calculations Workbook".

After I've done this, the next step is deciding which Calculations I need. When I have decided, I have two options

1. Press CNTL+Click on each calculation I intend to bring over

 2. If I want to bring all Calculations in a Folder, I Press SHIFT+Click, and then go down to the last calculated field in the Folder and Press Click. This will click all calculations in the folder.

The next step is to Copy them. Right Click on any calculated field and Press the "Copy" option

Once I have the Calculated Fields copied, I go over to my 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 I copied from the Calculations Workbook

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 my Calculations Workbook, all of my Date Calculations reference a Field called [Date]

The problem is there is no [Date] field in my Target Workbook, there is an [Order Date] field

So to Tableau, these are Invalid Calculations. 

Well, how do we fix this? 

Easy, we 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. To rename a field, simply right click, and press Rename.

After renaming [Order Date] to [Date], Tableau removes all of the Dependency errors, and my Calculations now are valid.

Well wait, what if I need that Field to be Called [Order Date]. No problem! Simply rename it back to [Order Date]. A really cool thing Tableau does when you rename fields in a Workbook, it automatically updates all of the calculations that use that Field as well.

Now, another cool thing you might notice is when I copy/pasted Calculations with Parameters, Tableau instinctively knew to bring over those Parameters with the Calculation.

So now, not only have I saved time from writing the calculated fields, I've also saved time from recreating the Parameters as well.

Another amazing thing Tableau does when I Copy/Paste from one workbook to another is bring over the Meta Data, like Comments. Now, not only have I saved time from writing calculations and parameters, I've also saved time in documentation. Best part, my documentation on Calculations remains consistent across my workbooks.

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. This can ensure your team collectively is always using the most efficient version of a calculation. The best part of this.....no XML hacks. This is 100% within the realms of what you are able to do in Tableau. Now while not commonly used in this way, it is supported (Disclaimer, *To my knowledge, this is supported*). 

*Note on creating your own Calculation Workbook*

• I like to name my base calculations according to their function, this helps me when I am Copy/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).
• Try to stay clear of Nested Calcs. 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.
• For my comments, I like to add two pieces of information
• A brief description of the calculated fields function
• 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.
• 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 this, it is better to create the calcs in the Target Workbook, rather than trying to Copy/Paste them over.
• 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.

Hope this helps!

Rody Zakovich