Have you ever needed to have all your Calculated fields in one single place? Or have you needed to know which calculated fields feed into which?
As Tableau workbooks grow in complexity, it can be difficult to know what each calculation does, or to remember what variables affect it. In the past, I’ve taken individual screenshots of each calculation and linked them all together in a “whiteboard”, to map out how different Calculations and variables interacted with one another.
Taking these screenshots can be quite time consuming, so today I’ll share a way for you to find all your Calculated fields in one go.
Note: I will take you through the semi “manual” solution today, but I’ll also work on a Python version which I’ll share as soon as it’s ready 🙂
Update from 10-Sep-22: I have written a Python script which automates the Calculated Field extraction, plus extracts other useful info from a workbook. You can find this code on my Github page. I will soon publish a blog post to explain how the code works and show you the really cool documents it produces! I will also explain which Tableau APIs I used.
How does the extraction work?
The “magic” behind this is related to the fact that digital files are often stored as XML data – although the users don’t usually see or interact with the XML. A Tableau file is no exception, so if we tap into the XML, we can extract what we need.
For what Tableau files does this solution work?
This works for unpackaged (.twb) or packaged (.twbx) Tableau workbooks.
What are the steps I must follow?
- Save your Tableau workbook (.twbx) into the folder of your choice, and open this folder with your File Explorer.
Note:
- If you have an unpackaged workbook (.twb) skip to Step X.
- If you haven’t got any local copies of your Tableau workbook, you can always download one from your Tableau Profile – this will download a packaged workbook (.twbx).
2. On your File Explorer, select your packaged Tableau Workbook and right-click on it. On the Menu that appears, click on Rename.
Change the “twbx” ending for “zip” and choose Yes to the system warning (ie. the pop up message asking “Are you sure you want to change it?”)
3. Your Tableau Workbook will now appear as a zip file. Select it and right-click on it to show the file menu. Click on “Extract All…” so you can unzip the file.
Note: you can also run this step in any other way or with any other software that will let you unzip the contents of the zip file.
4. Go to the location (ie. folder) where you unzipped the Tableau Workbook’s contents. In my case I unzipped it to the “Book list generator v3” folder.
Within that folder you will find an unpackaged Tableau workbook (.twb file). This is the file that we are interested in, as it holds all the main information behind our Tableau visualizations.
5. Right click on the twb file and choose the Rename option. This time change the “twb” part for “xml” – this means that your PC will start treating the workbook file as an XML file.
6. Right click on the xml file and choose to “Open with” any text editor of your preference. In my case I am using Notepad++ (which is free and really useful).
7. Now you will see all the contents of your Tableau workbook, in XML format! There is tons of information here for you to play around with and discover!
8. Now use a search function (eg. press Control-F) and search for “Calculation” – all the Calculated fields we created in Tableau will appear within a “calculation” tag – this means that they will appear within two “< >” symbols, as shown below:
In fact, the structure of the calculation tag is:
<calculation class='tableau' formula='yourOwnFormula' />
We can simply grab the bit that comes after the “formula=” part, as this would be the formula for our Calculated Field.
What about the Name of the Calculated Field?
The calculation tag (< calculation … /> is itself held within a “column” tag (< column > … </column>) – in this case we’d say that the column element is a parent to the calculation tag.
The Calculated Field from the previous screenshot (in xml format) is displayed in Tableau Desktop in the following way:
As you can see, this Calculated Field is called “Nominators per author”. If you look for this name within the xml, you will see it as
caption = 'Nominators per author'
This means that the name of each Calculated Field is found right after the “caption” part, within the “column” tag.
Since our aim is to find all the formulas for our Calculated Fields, we shall repeat the search for “Calculation” and extract the formula and caption for each.
Practical example:
To make things clearer, let’s use the example of the “Iteration test” calculation that I created a while ago.
- I will open the “Iteration test” Calculated Field from Tableau Desktop to show you the actual formula within the calculation.
2. Using the xml version of the Tableau workbook, I can search for “Iteration test” until I find its calculation tag (the < calculation … > bit).
Once I find tit, I can simply copy paste the formula part into a document where I’m keeping a log of all my Calculated Fields (eg. a word document, an excel doc, etc).
Note: If you do a visual comparison between the xml and the Tableau Desktop screenshots, you will see that the xml information matches the information shown to us via the Tableau Desktop app.
