Replacing Parameters with Calculated Fields

Version 1

    (Originally posted in the forums at: https://community.tableau.com/message/480904?et=watches.email.thread#480904)

     

    Maybe you've come across this need yourself and thought, "Man, this is going to take a lot of time to go through and find all the fields I need to change!"

     

    I have a workbook that a publisher of mine posted which used a date parameter. The value was that, across two data sources, the calculations would be based on the date the user selected in the parameter. But, as is the case when using parameters, this isn't very dynamic. For the last six months, users of this workbook had to keep changing the date in the parameter. Rather than republish this every few weeks, the publisher wanted to know how it could be made to be more dynamic.

     

    The short answer was to replace the references of the date parameter with a calculated field. Unfortunately, this affected several vizes and more than two dozen other calculated fields so there has to be another way than manually editing each calc! Using the right-click menu on the parameter you can find "Replace references..." but you'll also find that you can only replace parameters with parameters (dimensions with dimensions, measures with measures, and data sources with data sources, too). So if the built in feature isn't going to solve this issue, what can?

     

    This is where code hacking and knowing how Tableau saves files comes in!

     

    1. First, I saved a copy of the workbook as a .twb. This is very important because the .twbx would have to be unpackaged - save yourself a step and save as a .twb! .twb files are XML documents! This is a fairly human-readable format allowing you to search for the named columns you're going to work with.
    2. Next I opened this file with Notepad++ (or other similar text editors, but Notepad++ has some nice features and is free vs. Sublime).
      1. At this point I had to do some sleuthing. You see, though the formula that is visible in Desktop may look like this:
          IF [SpendDate] = [DateParameter]
           THEN [AverageValue] END

        the XML form of this would look like this:

           formula='IF [SpendDay] = [Parameter].[Parameter 1]
THEN [AverageValue] END'
    3. Now I know what to look for in the XML, I created a calculated field in Desktop that produced a dynamic date.Notice the name? It's not the friendly name of "Saturday", but the less friendly [Calculation_322007376789700614]. With this knowledge, I did a find/replace of [Parameter].[Parameter 1] with the unfriendly calculation name.
      1. Save the file (again as .twb) and reopen in Notepad.
      2. Search for the new calculation. This calculation is meant to provide the date of the previous Saturday:
            <column caption='Saturday' datatype='date' name='[Calculation_322007376789700614]' role='dimension' type='ordinal'>
                <calculation class='tableau' formula='date(datetrunc(&apos;week&apos;, today(), &apos;Saturday&apos;))' />
              </column>
    4. Save the text file then open in Desktop! All the calculations were updated to use the newly created calculated field rather than the old DateParameter! Now the XML shows:
          formula='IF [SpendDay] = [Calculation_322007376789700614]&#13;&#10;THEN [AverageValue] END'

      and displays in Desktop as:
         
      IF [SpendDay] = [Saturday]

          THEN [AverageValue] END

     

    That's it! Now I can delete the parameter without affecting anything!

     

    Was this helpful? How else might you take advantage of this technique?