<< Back

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:

  1. Pressing Alt, A, C (shortcut to create a calculated field) –or going as usual with the mouse– 45 times.
  2. Then copying and pasting the corresponding formula. Again, 45 times.
  3. And, finally, placing them in the desired worksheet and making sure that all of them, yes the 45 of them, were computing properly.

My approach?

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:

  1. Creating the calculated fields in Alteryx

Opening the Tableau workbook (.twb file) in a text editor shows several things:

  1. 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.


    Template calculated field.

     

  2. The “formula” inside the XML is converted into mostly HTML-encoded text.

    Formula as it is actually stored in the XML of Tableau’s workbook.

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.


From plain text to HTML. A filter applied to the HTML codes as it is not necessary to convert everything to the HTML codes.

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.


Extract the template calculated field from the workbook.

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.


Updating the template placeholder 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:


Generating the workbook with all the calculated fields based on the template. Download the Alteryx workflow here.
  1. 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:


Current Tableau’s worksheet.

What the same worksheet looks like in the XML:


Worksheet structure as seen in the XML.

A few takeaways of this structure:

  1. 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.
  2. Rows will have the measure values while “t” -not relevant here- will be in columns.
  3. 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.

  1. The first one is just the same one used earlier with the field name, alias, type, etc.


    Calculated field definition, the same as the one used earlier.

     

  2. The second one, “column-instance“, contains the specific information needed for this particular view, including how to compute it.


    This view’s specific information — detailing 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).


Obtaining the worksheet.

Next, the “Measures Names” filter can be updated to contain all the needed calculated fields:


Obtaining the updated “groupfilter”.

The calculated fields definitions obtained earlier (with name, type, etc.) need to be present also in the worksheet datasource-dependencies section:


Updating the calculated fields definitions.

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:


Specific definition with the how to compute for the particular view.

Finally, we need to restore the “placeholder” values in the workbook:


Restoring 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):


And just some more formatting left…

The full workflow in Alteryx:


Complete Alteryx workflow– download it here.

In summary,

  1. Tableau wants the formulas inside the calculated fields as HTML-encoded text.
  2. Placeholders are your friends when editing XML in Alteryx.
  3. 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!

Leave a Reply

Your email address will not be published. Required fields are marked *