Write-back for Everyone: Parameter Query Language for Tableau (Part 1)

Oct 12, 2020

by Rosario Gauna & Klaus Schulte

Write-back for Everyone

There are many use cases where it’s necessary to add user defined data to the existing data to do or to complete an analysis.

Some typical examples:

• Sales/profit planning or forecasting: What will product group/customer/region sales be at the end of the year/next year? What will profits be at the end of the year/next year?
• Completion status in project management: What are the current degrees of completion in our project portfolio?
• What If analyses: What impact do changes of variables have on our key performance indicators?

Unfortunately, you cannot create this data in Tableau without further ado, because Tableau wasn’t built to create data. There is of course a way to create data in Tableau, using parameters for every variable of the analysis on the required level of detail. But this would only be practicable if the analysis was static and the number of parameters needed for the analysis was still manageable. But this won’t be the case in most of the examples like the ones mentioned above.

In general, there are three ways to deal with this.

1. Create the necessary data outside of Tableau and join it together with the original data in Tableau.
2. Take the original data and complete the analysis outside of Tableau in another application, for example in a spreadsheet.
3. Use an extension that brings in the required functionality.

Major drawback of the first two ways is that you have to leave Tableau to do parts of or even the entire analysis. And extensions cause additional costs and often cannot be used for security reasons/policies. 

So, wouldn’t it be great to create, store and use this data directly in Tableau and without extensions?

In analogy to SQL, this post will introduce a Parameter Query Language to use a Tableau parameter like a database. It will allow users to store and read any user defined data and hence enable all the use cases where additional user data is necessary.

Previous Work to Build on

There have already been some people in the Tableau community who wrote about using Tableau parameters as a datasource. Zen Master Jonathan Drummey introduced the general idea in this post, where he explained that with the introduction of Parameter Actions in Tableau version 2019.2 an easy way of controlling the contents of a parameter had become available. Only recently, Zen Master Marc Reid talked about using parameters as a datasource in a talk at the Belgium TUG where he shared this presentation explaining the general concept and a workbook with a couple of use cases.

Second basic concept this post builds on is the implementation of an apply button for parameters. Unlike with Tableau filters, there are no apply buttons available for Tableau parameters. In this post, Tableau Public Ambassador Brian Moore describes a technique where he stores the changes of a parameter in a buffer dimension and then runs a parameter action on this buffer dimension. Zen Master Ken Flerlage also leverages the technique in this post.

Sales & Profit Forecasting

This post will introduce PQL drawing on the example of a Sales & Profit Forecasting use case. The workbook is available on Tableau Public and uses reshaped and aggregated superstore data which can be downloaded here.

• Forecasts are calculated monthly on Category and Region level.
• RoY (rest of year) sales are forecasted based on PY (prior year) RoY sales. Users are asked to adjust PY RoY sales to their planning.
• RoY profits are forecasted based on an estimated RoY profit margin.

Parameter Schema

First thing we have to do is to create a string parameter.

There are no character limitations for string parameters and the only thing to keep an eye on when defining the level of detail to collect user data on is the performance of the final workbook, especially when deployed in a server environment.

In the example workbook, the parameter where all the values get stored in is the Buffer parameter.

Let’s have a look at the Buffer parameter after it had already been populated with data.

Like described in Jonathan’s and Marc’s posts, the basic idea is to create a delimited list, where the values within this list are separated by a delimiter (like the famous comma separated values in .csv files). 

Let’s have a closer look at our delimited list to learn about the “schema” we are using in this example:

||2020-08-Technology-South⮕|M1=0.14000000|M2=0.20000000|M3=■2020-08-Technology-South||Carriage Return (Cr)

Every “record” in our “table” has the same syntax:

1. Prefix: The level of detail user data identifier.

||2020-08-Technology-South⮕

• The Prefix starts with two vertical separators “||” (this can be any uncommon character or combination of characters)…
• …followed by a key defining the level of detail user data is created on (here: Year-Month-Category-Region).
• A “⮕” indicates the start of the area where user data is stored (can also be any other uncommon character or character combination).

2. Metrics

|M1=0.14000000|M2=0.20000000|M3=

• In our example, we are using two other parameters to create the user data, one to define the adjustment for prior year RoY sales, and another one to define the current year RoY profit margin. 
• The data created with the first parameter is stored after the delimiter “|M1=” and the data of the second parameter after the delimiter “|M2=”. 
• “|M3=” isn’t followed by user data, it just needs one more delimiter than metrics you want to create.

3. Suffix: The level of detail user data identifier.

■2020-08-Technology-South||Carriage Return (Cr)

• “■” indicates the end of the area where user data is stored.
• Then we see again the LoD-key Year-Month-Category-Region…
• …and two vertical separators “||”.
• In addition, we’ve added a carriage return to get a line break after every “record”.

This schema of prefix and suffix will help us to identify the metrics stored in the buffer parameter, since it allows us to associate each metric with the key defined at the level of detail user data is created on. And this is what will allow us to read back our user data from the parameter at a later step.

Insert/Update data in Buffer Parameter

In the following definition of formulas, we intentionally avoid using the SPLIT, FINDNTH and REGEX functions, since although they are very useful, they are not available for all data sources, e. g. for SAP HANA live connections.

Insert data

To insert data into the parameter, we have to re-construct our schema in a dimension on which we will then run a parameter action.

[New Buffer] (step 1)

"||" + [Key (level of detail)] + "⮕" + 
"|M1=" + LEFT(STR([Adjusted PY_ROY]),10) + 
"|M2=" + LEFT(STR([Adjusted Profit_Margin]),10) + 
"|M3=" +
"■" + [Key (level of detail)] + "||" + CHAR(13)

[this formula has to be completed in the next step]

We can see all the elements from our schema here, including the LoD-key which we have referenced from another field.

[Key (level of detail)]

[Year_Month] + "-" + [Category] + "-" + [Region]

In this example, there are two metrics, it could be more or less as necessary for the analysis. Other variables like for example scenario labels (“best”, “average”, “worst”) could be included as well.

Converting decimals to strings sometimes results in infinite long strings. The LEFT function prevents this here and limits the number of characters to 10. In this particular example, we could have used less characters. In general the number of characters has to be adopted to the specific use case. 

The carriage return can be achieved with CHAR(13); this will add a line break after every “record” of our list of data. 

If we would now just add “+[Buffer]” at the end of this formula, we would chain a new record at the beginning of the buffer parameter with every hit on “Update”. But wanting to also update records, unfortunately this will be just a tiny little bit more complicated.

Update data

To be able to update a record, we will not just add the Buffer parameter, but will also find the Current Record in Buffer and replace it with an empty string. In other words: we are deleting the current record from the buffer at the same time we are concatenating Buffer with the new record.

[New Buffer] (step 2)

"||" + [Key (level of detail)] + "⮕" +
"|M1=" + LEFT(STR([Adjusted PY_ROY]),10) +
"|M2=" + LEFT(STR([Adjusted Profit_Margin]),10) +
"|M3=" +
"■" + [Key (level of detail)] + "||" + CHAR(13) +

REPLACE([Buffer], [Current Record], "")

It needs three steps to find the current record in our parameter:

[Current Record Start]

FIND([Buffer], "||" + [Key (level of detail)] + "⮕")

Current Record Start gives back the position where the current record starts, e. g. at position 1,234.

[Current Record End]

IIF([Current Record Start] = 0, 0,
FIND([Buffer], "■" + [Key (level of detail)] + "||" + CHAR(13)) +
LEN([Key (level of detail)]) + 4 )

Current Record End gives us the end position of the current record, e. g. at position 1,300. It finds the beginning of the Suffix first, and then adds the length (aka the number of characters) of this part.

[Current Record]

IIF([Current Record Start] = 0, "",
MID([Buffer], [Current Record Start],
[Current Record End] - [Current Record Start]))

In case we actually have a current record, the MID function gives us back the current record. MID needs three arguments: the string parameter to search in, Current Record Start (1,234) and Current Record length (1,300-1,234=66).

Execute Insert/Update

That’s pretty much it for inserting and updating data in our “database”. All the magic then happens in the Update sheet:

Selected Period and Selected Dim are controlled by Parameter Actions from the main visualization and filter down the data to the level of detail defined in our Key (level of detail) dimension.

The action to actually write the data into the parameter is then finally implemented on the dashboard.

Select data from Buffer Parameter

In order to obtain or extract the data of the metrics that we require, we again have to calculate the start and end positions of each one of them within the “Current Record”. 

The process is similar to the one we already carried out when obtaining the “Current Record” of the Buffer parameter, but now we will compact the three steps into a single formula per metric.

[Ask User Metric1]

ROUND(
IIF([Current Record Start] = 0, 0,
FLOAT(
MID([Current Record],
FIND([Current Record], "|M1=", 1) + 4,
FIND([Current Record], "|M2=", 1) -
FIND([Current Record], "|M1=", 1) - 4)))
, 2)

We again use the MID function to retrieve all the characters that are found after the occurrence of the string “|M1=” and before the occurrence of the string “|M2=”.

[Ask User Metric2]

ROUND(
IIF([Current Record Start] = 0, 0,
FLOAT(
MID([Current Record],
FIND([Current Record], "|M2=", 1) + 4,
FIND([Current Record], "|M3=", 1) -
FIND([Current Record], "|M2=", 1) - 4)))
, 2)

For our second metric, we apply the MID function accordingly.

Both metrics can now be used in our main visualizations, like Metric 1 in the Sales forecast below.

Saving User Data

The parameter data can be saved locally in the workbook.

A more convenient way — especially when working collaboratively on a forecast like this, is to save personalized views on Tableau Server or Tableau Online. In custom views, users can save their preferences regarding filters or sortings, as well as the values captured in parameters.

It is important to know that a custom view does not change the original view, but remains related to it. If the original view is updated or republished, your custom view is also updated. Similarly, if the original view is deleted from the server, your custom view is deleted.

Once the required changes have been captured, we can save the view, for which you must click “View” in the toolbar. 

Then enter a name of your preference for your custom view, select if you want it to be the default view you see, and if it should be public so that other users can access it. And click Save.

A custom view can be rewritten as many times as required, to update its filtering preferences and/or update the parameter values.

By default, a custom view is private, so only you will see it in the Custom Views dialog. But if your role on the site is Explorer or Creator, you can make a view public so that anyone with permission to access the original view can also see its customized version in the “Other Views” list.

Although private custom views are hidden in the “Other Views” list, the URL can be shared with anyone else who has permission to access the original view so that they can access it.

Wrap Up

In this post, we’ve introduced Write-back for Everyone, a Parameter Query Language that allows us to insert and update user-created data into a string parameter and read back the data from it to use it in visualizations and dashboards. These values are stored in the workbook. On a Tableau server, they can be modified and accessed afterwards through custom views.

Like said at the very beginning of the post, we see a lot of use cases for this technique. Imagine On-the-fly-What-If analyses in the boardroom or forecast exercises like we did in our example workbook.

We are looking forward to seeing what you will do with this technique and are very much looking forward to getting your feedback on this. Please leave your feedback in the comments below or reach out to us on Twitter where you can find us at @rosariogaunag and @ProfDrKSchulte.

In Write-back for Everyone: Parameter Query Language for Tableau (Part 2) we will work with a data source that provides predefined values for the metrics, so that a user in Tableau only needs to capture the values they want to modify. The post will also deal with downloading the user data that has been created and making it available for further analyses.

Thanks to Marc Reid for feedback along the way!