XML Hack to Change Calculated Fields in Tableau


Have you ever needed to do a bulk change of formulas in a Tableau workbook? Or have you ever needed to check that all formulas are correct or that they are working off the correct field in the data source? Or even to find out if you were to change a calculation what views would be affected? Then this XML hack is your friend.

If you didn't already know, all Tableau workbook files (that's .twb NOT .twbx) can be opened in a Notepad or similar and are just plain old XML underneath. The XML hosts a treasure trove of workbook information. You can use it to change the version of Tableau the workbook will open in. You can also use it to change the data source without losing all your formatting

What's also really useful is that you can use the XML to inspect or change calculated fields. Or even just to find out if you were to change the calculation which views would be affected. 

**WARNING**
This is all done at your own risk; playing around with the XML of a .twb file can corrupt your workbook. Always take a copy first.

Changing a calculation:

The first step is to open up the .twb file in Notepad or Excel or similar. I'm using Notepad++. Find your .twb file and right-click and select open with and find Notepad or Excel. If you're using Notepad++ in that right-click menu you'll handily find "Edit in Notepad++".

Once you have the XML open search for your calculation name. Here's a KPI calculation I have:

Here's what it looks like in XML:

 <column caption='KPI' datatype='string' name='[Calculation_1748522558830104577]' role='measure' type='nominal'>
        <calculation class='tableau' formula='IF SUM([Profit])&lt;10000&#13;&#10;THEN &quot;Poor&quot;&#13;&#10;ELSEIF SUM([Profit])&gt;=10000 AND SUM([Profit])&lt;50000&#13;&#10;THEN &quot;Okay&quot;&#13;&#10;ELSE &quot;Good&quot;&#13;&#10;END' />
      </column>

You'll notice that some characters are replaced with HTML entities. For example a less than sign (<) is displayed as "&lt;". So you will have to use a reference (here's a good one: http://www.w3schools.com/html/html_entities.asp) in some cases like the KPI calculation above. However I can still easily change things like what Functions are used in the calculation, or what hard coded numbers or text are used. In the KPI calculation, I could for example change the KPI ranges.

 <column caption='KPI' datatype='string' name='[Calculation_1748522558830104577]' role='measure' type='nominal'>
        <calculation class='tableau' formula='IF SUM([Profit])&lt;20000&#13;&#10;THEN &quot;Poor&quot;&#13;&#10;ELSEIF SUM([Profit])&gt;=20000 AND SUM([Profit])&lt;60000&#13;&#10;THEN &quot;Okay&quot;&#13;&#10;ELSE &quot;Good&quot;&#13;&#10;END' />
      </column>


Here's a simple example of a calculated field that simply says "Test":

<column caption='Test' datatype='string' name='[Calculation_2155253899910307840]' role='dimension' type='nominal'>
        <calculation class='tableau' formula='&quot;Test&quot;' />

This is an easy change to make the calculation read "Hack" instead:

<column caption='Test' datatype='string' name='[Calculation_2155253899910307840]' role='dimension' type='nominal'>
        <calculation class='tableau' formula='&quot;Hack&quot;' />

Ta da!

Finding which views use your calculated fields:

Searching the XML for the name of your calculation can also show you which views use it and which will change if you edit your calculation. If I search my XML for the 'KPI' calculation I find that it's being used in a worksheet called KPI too.



If you carry on searching through your XML you can watch out for all the "worksheet name =" tags and note down each view where the calculation is used.

Of course if you're really clever you could even use this XML knowledge to build scripts to run checks on workbooks to see which views use which calculated fields, so you know if you change them what sheets will be affected. The possibilities are endless!

CONVERSATION

0 comments:

Post a Comment

Back
to top