Creating 50 Tableau Table Calculations in Alteryx (and Placing them in a View with a single workflow)
This is modified from a longer post here.
I recently found myself in a personal project where I needed to use 45 table calculations in a single view. I had the plain text for the formulas (coming from a previous Alteryx workflow) and I didn’t feel like:
- Pressing Alt, A, C (shortcut to create a calculated field) –or going as usual with the mouse– 45 times.
- Then copying and pasting the corresponding formula. Again, 45 times.
- And, finally, placing them in the desired worksheet and making sure that all of them, yes the 45 of them, were computing properly.
Take the plain text formulas to create the 45 calculated fields using Alteryx. Then edit the XML of the workbook to drop them in the appropriate sheet with the instructions to compute properly.
Let’s break that down:
Creating the calculated fields in Alteryx
Opening the Tableau workbook (.twb file) in a text editor shows several things:
- Calculated fields are not comprised of “just the formula“: they require also some other attributes: name, alias, type,etc. To make things easier, I’ve first edited the workbook in a text editor to create a template calculation with some placeholder values.
- The “formula” inside the XML is converted into mostly HTML-encoded text.
Let’s start with the HTML-encoding since it is pretty straightforward –provided one has a list of symbols and their corresponding HTML code at hand. Starting with an Excel file that contains the multiple formulas (that I created using Alteryx elsewhere), and using the “Find and Replace” tool in Alteryx feeding that reference table it just takes clicking a button to obtain the HTML-encoded text that can now be copied into the XML file.
Now that the HTML is ready, on to injecting the new calculated fields into the XML. First, I extract the “template” formula I created above and use it two ways: a) to put a placeholder in the workbook that will be replaced by the output of b) to use it as a template for the multiple calculations.
Appending that template to all the calculations, a few formula tools can be used to replace the PLACEHOLDER_CAPTION, PLACEHOLDER_NAME and PLACEHOLDER_FORMULA with the respective values.
The only thing left is replacing that placeholder obtained at (a) earlier by the output of the summarize tool and save the file. At a bird’s eye:
Placing the calculations in the appropriate worksheet
This is going to follow the same approach as above: having a look at the XML and then using placeholders to update with the desired values.
What my worksheet looks like in Tableau right now:
What the same worksheet looks like in the XML:
A few takeaways of this structure:
- It seems clear that, given that we are using “Measure Names”, to be able to add additional calculated fields we have to edit the “categorical filter”. We’ll need to add as many “Groupfilter” sections as calculated fields.
- Rows will have the measure values while “t” -not relevant here- will be in columns.
- To compute the full-range of padded (“toPad [bin]“) we’ll need to show empty rows from the table layout option.
Expanding the “datasource-dependencies” will reveal that more things need to happen: two entries are needed for each of the calculated fields that we want to add to the view.
- The first one is just the same one used earlier with the field name, alias, type, etc.
- The second one, “column-instance“, contains the specific information needed for this particular view, including how to compute it.
Using as input the .twb file (read as .csv, with no delimiters and concatenating everything in a single record), we can extract the information for the worksheet (named TEST in this example).
Next, the “Measures Names” filter can be updated to contain all the needed calculated fields:
The calculated fields definitions obtained earlier (with name, type, etc.) need to be present also in the worksheet datasource-dependencies section:
These definitions do not include the information needed to compute the table calculations in the current view. These are part of the “column-instance” section that can also be updated:
Finally, we need to restore the “placeholder” values in the workbook:
and… we start from a test worksheet with a template calculation to a worksheet with all the 45 calculations computing properly. The only things left are changing the graph to line, adding “measure names” to colour and…(formatting, always formatting):
The full workflow in Alteryx:
- Tableau wants the formulas inside the calculated fields as HTML-encoded text.
- Placeholders are your friends when editing XML in Alteryx.
- The worksheet needs to have the regular definition of the calculated field as well as a column-instance to instruct on the specific requirements of the view.
Thanks for reading!