One of my favorite recent projects at PATH has been creating a decision-making dashboard to support indoor residual spraying vs. malaria in Zambia for the Malaria Control & Elimination Project in Africa (MACEPA). The goal is to use up the remaining bottles of insecticide spray left over from the first round of spraying by appropriately targeting areas presently experiencing higher rates of malaria.

The dashboard was designed so that users can click on the circles (community health worker catchments) and/or the grey blobs (the pre-defined spray areas containing dozens to hundreds of structures) to ultimately generate a list of spray areas that can be exported. I first built this using Tableau v2018.3’s Set Actions, but there was a challenge controlling the interactivity: It’s all too easy to accidentally click on the wrong mark, click in an empty area, not be holding Shift down at the right instant, etc. and end up triggering the action and resetting the selected list of spray areas.

I was having a conversation with my one of my colleagues about a related use case and she encouraged me to look this more, and I thought that what I really wanted was to be able to treat the destination list as a separate “data source” that we could easily & directly add or remove values from…and then I thought about parameters as a separate data source*** and Tableau v2019.2’s Parameter Actions now give us a way to easily control the contents of that parameter source. In a few minutes I had a proof of concept, then I expanded that to create a suite of primitives for adding & removing (via parameter actions) & querying the list of values.

*** I can’t say that idea is unique to me. A long time ago I’d noticed that in the Tableau workbook XML that Tableau’s parameters were actually stored in the section, and awhile back Mark Jackson built his amazing Monopoly viz by storing the game state in parameters.

So now users can click on either a community health worker catchment or spray area to add it to the list, and then from either the inset map view or using a menu action remove the selected item from the list:

Here’s another use case for this technique: fantasy league drafts. For this the user starts out with a list of players and then can “draft” players into their team and also mark which players are on other peoples’ teams. There are 6 basic actions: add to my team, add to their team, remove from my team, remove from their team, and then clear my team and clear their team. This is all done with calculations and parameter actions:

The rest of this post describes the key differences between set actions and parameter actions, how to use parameter actions as a data source, and links to a workbook with a number of useful calculations built out.

What are the differences between a Set Action and a Parameter Action?

Beyond the obvious difference in that set actions (introduced in v2018.3) update sets and parameter actions (v2019.2) update parameters there are a variety of differences that define what we can do with each.

Fundamentally a parameter action takes the value from the selected mark (or an aggregation result from the selected marks) and then assigns that value to the parameter, then triggers a viz refresh. The viz refresh is exactly the same as when we manually change a parameter value: Tableau uses the new value in any evaluations of that parameter – in calculations, for display in views, in reference lines & bins & top N filters, etc.

• Source of values: Set actions get their values from a single field in one data source. Whereas one or more fields (even from different sources) can be used to assign values to a parameter action, where different fields can be configured as the source of different actions that all target the same parameter. For example we could have multiple menu parameter actions that have different effects on the same parameter.
• Level of calculation origin: The origin field used for a set action must be a record-level field. Whereas for parameter actions the origin field can be record-level, aggregate, or a table calculation.
• Required viz Level of Detail (vizLOD): Set actions require that the record level value that the set is based on is present in the viz. Since it’s a record level value that means it’s a dimension in the view that can change the viz Level of Detail (vizLOD). Sometimes that can create a situation where the vizLOD is more fine-grained than we’d like and then we have to change the view and/or other fields in the view in ways that create more complexity. While parameter actions require that the origin field of the parameter action is in the view the fact that the origin field can be at any level of calculation (see prior point) means that we can use an aggregation or even a table calculation and not change the vizLOD.
• Data source (in)dependence: Sets presently (as of v2019.2) only exist in a single data source…occasionally we can use them as part of a data blend but that only works for some particular use cases. Whereas parameters live “outside” any particular data source.
• Available controls: As of v2019.2 the only way to control the membership of sets through set actions is to create a viz to use as the origin of the values. Whereas parameters already have a rich set of controls (dropdown, single select radio button, etc.) for parameter lists & ranges and we can use those in conjunction with parameter actions.
• Single select vs. multi-select. Set actions support single- and multi-select, in fact there’s no easy way to enforce single select for a set action (or filter action). As of this writing parameters (and therefore parameter actions) in Tableau can only have a single value, so Tableau’s provided parameter controls support only single select. With the new parameter actions in v2019.2 we can choose whether we want to enforce a single select (if a user does a multi-select then the parameter is not updated) or do an aggregation of the selected multiple values to a single value for the parameter. For example for numeric parameters we have the usual aggregations (sum, avg, min, max) plus some summary statistics:

As of v2019.2 for date & text data types the parameter action aggregations are limited to min & max.

• Single value vs. multiple value aka multi-valued. Sets inherently support multiple values. Parameters support a single value…though this post is about a way to get around just that!

That’s a laundry list of differences between set actions and parameter actions. Now let’s talk about filter actions.

Why not use a filter action?

Filter actions can only include or exclude values from the target, and in addition each filter action overwrites the prior selections. So if I’ve just selected set of marks A and I want to add another set of marks B then with filter actions (or Set Actions) I have to carefully select all of A and B and then trigger the action. This is really challenging when all we often want to do is just append (or remove) a few marks.

In addition the filter action value(s) have to exist in the target data source, otherwise there’s no way to validate them. Since parameters are in a sense a completely separate data source then we can use whatever values we want.

What about the Javascript API?

The JS API could of course do all this, I personally find it useful to have multiple ways to accomplish the same task. 🙂

What this looks like

Here’s a flow chart of the the configuration:

So the list parameter is a string list of values that we’re adding to, removing from, etc.

How to build views and dashboards using parameter lists

The parameter as a data source workbook (presently a Dropbox download and you’ll need the v2019.2 beta to open it) has a wide variety of calculations for adding & removing values from the parameter list source as well as querying the parameter. Here’s how to use it:

1. Copy field(s) to your workbook

You can copy & paste these calculations & parameters from the workbook into your own workbook. There are four folders of calculations:

• Param as DS Action Strings – these are calculated fields that you will use as the source values of parameter actions. Copy the fields for the action string(s) you want to use.
• Param as DS Core Fields – these fields are used in configuring the other fields, testing whether the delimiter is ok to use, and in other calculations. Copy all of these fields.
• Param as DS Core Queries – these are queries used in other action strings & queries. Copy all of these fields.
• Param as DS Queries – these are calculated fields that provide information about the current state of the parameter, for example testing whether a given value is in the list, counting how many times a value is in the list, etc.

After copying the field(s) over you’ll then do the following:

2. Determine the unique value for identifying marks

We need to have a unique value for item item we want to store so we can track it. It might be something like a Customer ID, or a login name, or something else. You’ll need to pick a field or combination of field(s) that uniquely identifies the value(s) you want to add and then put that into the Unique Value field as a string.

Note that if your desired unique value is intended to be an aggregate or table calculation then the you’ll need to make sure that the aggregation is done before the Unique Value. Also note that this will turn a number of the action & query fields into measures, so do set up the aggregate before you build any views using those fields.

3. Determine the delimiter

This needs to be a character or combination of characters that don’t existing the data so that way we can separate values in the list. The Delimiter Param parameter holds the delimiter value and then that gets used to create the Delimited Unique Value field:

Delimited Unique Value

[Delimiter Param] + [Unique Value] + [Delimiter Param]

For the calculations can’t work the delimiter value can’t exist in anywhere in the Unique Value field. You can test the delimiter on your data using the Delimiter Test dimension measure in a view: ~screenshot of delimiter test.

Note that the delimiter can be more than one character, so for example if you want to use : as your delimiter but there are some fields that have a : you can use :: or even :::.

Also note that the Nth fields like “Insert (at Nth position)” and “Find Nth position of value” use FINDNTH() and/or REGEX functions that are not presently available for table calculations. So if you’re trying to base the parameter on table calculations and want to use the Nth logic you’ll have to build that with more primitive functions. If you’d like this functionality in Tableau then please vote for this feature request:REGEX…, SPLIT(), and FINDNTH() support for ta… |Tableau Community Forums.

4. Add action string(s) to your view(s)

For each action that you want to set up you’ll need to add the associated action string to the view, typically on the Detail Shelf with either Include in Tooltip turned off or a custom tooltip configured.

Basic action strings

Append (to list):

[List Param] + [Delimited Unique Value]

Insert (at beginning):

[Delimited Unique Value] + [List Param]

Remove (from list):

REPLACE([List Param], [Delimited Unique Value],'')

Reset list:

''

Then these action strings can be combined, for example:

Append (and remove other instances):

[Remove (value from list)] + [Delimited Unique Value]

A complete list of supplied operations for action strings is below.

5. Configure each parameter action

The parameter action configuration is done much like filter or set actions: You choose the source sheets or dashboards, how the action will be triggered (hover/select/menu), the target parameter and the field used for the value. In addition you have to choose what to do if the user selects multiple marks, for v2019.2 and this particular use case I’ll typically choose “None”.

6. Use the Param as DS Queries to build out display, calculations, etc.

The Param as DS Queries and Param as DS Core Queries folders have a variety of useful calculations, for example testing whether a value exists in the list, finding out where that value is in the list, and so on. A list of those is below. These can be used in any way you can imagine!

Basic queries

Value Exists in List – test whether a given value is in the list:

CONTAINS([List Param], [Delimited Unique Value])

Get Size of List – count how many items are in the list:

(LEN([List Param])
- LEN(REPLACE([List Param], [Delimiter Param], '')))
/ (LEN([Delimiter Param]) * 2)

Find Nth Position of Value – if an given value is in the list N+M times find the string position (from 1 to the length of the parameter) of the Nth value, otherwise return Null.

FINDNTH([List Param], 
    [Delimiter Param] + [Unique Value] + [Delimiter Param],
    [Nth Position])

Get Nth Item – Uses a parameter for N where N = 1 to some number to return the value at the Nth position.

REGEXP_EXTRACT([List Param],
    //start a non-capture group, then use a
    //non-capture group inside that (not sure why, but it
    //works to keep the delimiters out)
    '(?>(?>' + [Delimiter Param]
    //get the non-delimiter characters in a capture group
    + '([^' + [Delimiter Param] + ']+)'
    //back in the non-capture group
    + [Delimiter Param] + ')'
    //get the Nth pattern of the capture group
    + '{' + STR([Nth Position]) + '}' + ')')

A complete list of operations for queries is below.

List of operations

These are the operations (configuration, action strings, and queries) that have been configured in the parameter as a data source workbook (presently a Dropbox download and you’ll need the v2019.2 beta to open it).

Param as DS Action Strings

• Append (to List) – Append value to the list. If the same value is used for the parameter action multiple times then it will be repeated in the parameter’s value.
• Append (and remove other instances) – This version of append clears out all prior instances of the selected value from the parameter before appending it to the parameter.
• Insert (at beginning) – Inserts the value at the beginning of the parameter.
• Insert (and remove other instances) – Inserts at the beginning and like the Append (and remove other instances) ensures that the inserted value is unique.
• Insert (at Nth position) – Inserts the value at the 1st, 2nd, 3rd, Nth position in the parameter list depending on the value of the Nth Position parameter. The old Nth value and successive values are “pushed” to the right.
• Insert (at Nth position and remove other instances) – acts like the other remove other instances while inserting at Nth position.
• Remove (value from list) – Removes all instances of the given value from the parameter.
• Remove First Item – removes the first item from the list.
• Remove Last Item – removes the last item from the list.
• Remove Nth Item – removes the Nth item from the list based on the Nth Position parameter.
• Rest list – resets the list to an empty string.

Param as DS Configuration

• Delimited Unique Value – The Unique Value surrounded by the delimiters. This is used in almost all other calculations.
• Delimiter Test – A dimension that identifies whether the selected delimiter appears anywhere in the values for the Unique Value field.
• Unique Value – The field or combination of fields used to generate the value(s) to add & remove from the list. This must be a string data type.

Param as DS Core Queries

• Find Nth Item Position – finds the string position (from 1 to the end of the string) of the Nth item on in the parameter list.
• Get Size of List – counts the number of items in the list.

Param as DS Queries

• Count (# of times value is in list) – counts the number of appearances of the given value in the list.
• Find 1st Position of Value – finds the starting string position of the first appearance of the given value in the list.
• Find Last Position of Value – finds the starting string position of the last appearance of the given value in the list.
• Find Last Position of Value – finds the starting string position of the Nth appearance of the given value in the list, where the Nth is the Nth Position parameter.
• Get First Item – returns the value of the first item in the list.
• Get Last Item – returns the value of the last item in the list.
• Get Nth Item – returns the value of the Nth item in the list, where the Nth is the Nth position parameter.
• Get Length of Item at Nth Position – returns the string length of the item a the Nth position.
• Return Distinct Values – This uses a regex to return the distinct or unique values in the parameter list, however due to my lack of regex skills and/or Tableau’s lack of full support for all regex features this calculation re-orders the list.
• Value Exists in List – Test whether the given value exists in the list.

Using the parameter as data source calculations to build a fantasy league draft visualization

Here’s a quick overview of how I used the parameter as a data source workbook to build the fantasy league draft viz shown here:

1. First of all I determined that I was going to need two lists, one for “my” team and one for “their” team.
2. I also determined the set of operations I was going to need:
   1. Append (to list)
   2. Remove (from list)
   3. Reset List
   4. Value Exists in List
3. Then I copied the calculations from the parameter as a data source workbook.
4. The calculations were renamed to have “My” in their name, for example the List Param became My List.
5. Then I duplicated the My List parameter and renamed that to Their List, then duplicated and calculations to have them point at the Their List parameter. Now I had a full set of calculated fields to use to set up parameter actions and views.
6. I then created an Available Players view where I used a multiple axis crosstab to give me separate marks cards (so I could have separate pills on each controlling separate parameter actions). The first axis (for my team) had the Add to My Team and Remove from Their Team fields, with parameter actions configured to affect the respective lists.
7. I put the On My Team and On Their Team fields both onto Color to initially check that the values were accurate.
8. Then on the second axis (for their team) I added the Add to Their Team and Remove from My Team fields to detail and configured the parameter actions.
9. After testing that everything worked I moved the On My Team and On Their Team pills to Filters and set both to False, so the Available Players list only shows unselected players.
10. Then I created a worksheet for My Team that used the On My Team filter set to True and added the Remove My Team field to detail to use to generate a parameter action to remove with a click.
11. That worksheet was there duplicated and fields replaced for Their Team.
12. For the Reset My Team button I created a worksheet with the Reset My Team field and a parameter action.
13. And then that was duplicated to reset their team.
14. And then the dashboard with a bit of drag & drop.

Conclusion

Beyond the spray area selection and fantasy league drafts there are many other use cases for this technique -building games, creating surveys or quizzes inside a viz, dynamically re-sorting marks (for example moving a top level mark to the back), and all the different possibilities for creating cohorts on the fly.

I also don’t think this post is gong to be the last way to do this;Tableau has been adding a lot of features for interactivity in the last couple of years (highlighters, selection by category, set actions, extensions, etc.) and my expectation is that we’ll see new ways for configuring interactivity!

Here’s a link to the the parameter as a data source workbook (presently a Dropbox download and you’ll need the v2019.2 beta to open it).