How to Add Flexibility to Calculations with Parameters
Adding Flexibility to Calculations with Parameters
Parameters in Tableau Software are one of the more interesting and valuable aspects of the tool (when they work).
Parameters empower information consumers to change the content that appears in worksheets and dashboards. Basic parameter controls can be created using embedded options for a limited number of common use cases. Advanced parameters offer the ability to create parameters to address more unique “use cases” at the expense of a little more time developing parameter control.
Parameters are dynamic values that can replace constant values in calculations, filters, and reference lines. A Parameter is any value passed to a program in order to customize the program for a particular purpose. A parameter could be anything: A string of text, a range of values, or an amount just to name a few.
The basic parameters are variables that are provided in specific situations that reduce the number of steps required to create a parameter control. Basic Parameters are available to make a flexible top or bottom filters for a specified number of items in a set. In histograms, a parameter can be added that allows users to specify the size of each bin. Reference lines include a parameter option that provides a way to make the reference line change based on a user-selectable parameter value. Below diagram shows the three Basic Parameter controls in action.
Basic parameter controls diagram
The histogram on the top of above diagram displays order counts by the Size of Orders. The Sales Bin parameter allows the end user to change the size of each bin. The Parameter Size Range is from $500 to $10,000. The bullet graph in the lower left of above diagram compares sales (bars) to prior year sales (black reference lines) for every product name. The data set includes over 1,000 product names. The parameter allows the user to change the number of products displayed through a flexible top down filter. You can see that currently the top 15 products are being displayed. The scatter plot in the lower right includes a reference line called Profit Threshold that allows the user to change the threshold value and change the position of the reference line and the corresponding shading below the line.
All of these are Basic Parameters that are selectable options for these uses. Parameterizing a histogram’s bin size is accessed via a right-click on the bin field name that appears on the dimensions shelf. The flexible filter in the bullet graph is accessed by right-clicking on the product name dimension and selecting the Top tab in the filter dialog. The reference line parameter is accessed when adding the reference line by clicking the Value drop-down selector and picking the Create a Parameter Option. Below diagram shows each of the menus.
While basic parameters are very easy to create, they are also currently limited to the specific use cases as shown in Below diagram. Top or bottom filters, bin sizing, or flexible reference lines; if you want to create more advanced parameters, these require a little more effort.
Dialog windows for basic parameter creation diagram
Advanced parameters control is limited only by your imagination. You can create multiple parameter controls. Parameter controls can be chained together to create linked parameters. An entire book could be written on parameter controls because they provide programming-like functionality to Visualizations. Creating Advanced Parameter controls require three or four steps:
- Create the parameter control.
- Expose the parameter control on the desktop.
- Use the parameter in a calculated value (optional).
- Use the calculated value in the view.
If the parameter is being directly placed in the Visualization, it may be unnecessary to create a Calculated Value. The key point is that whatever the parameter is being used to change (typically a formula variable), that item must be used somehow in the Visualization in order for the Parameter Control to work.
The most popular use cases for Advanced Parameter is that it permits users to change measures or dimensions being displayed in a view. The technique in either case is the same. Below diagram shows a Time Series chart in which a parameter is being used to change the measure plotted.
Using a parameter to change the measure displayed in a view
The Parameter Control appears below the Marks card in a radio-button style filter. It allows the user to select three different measures for the time series chart. Currently the view shows profit dollars. Notice that the title of the worksheet includes the parameter and the axis label also changes.
Adding a Parameter Description to the title bar is done by double-clicking on the title bar and selecting the parameter used in the view. To add the Parameter Name to an axis, drag the parameter from the Parameters shelf to the axis. Then edit the axis and erase the static title. This example also rotated the parameter label and removed the label heading. When a new selection is made from the Parameter Control, the Visualization will change along with the headings and reference line to reflect the selected value.
Creating the parameter control:
Defining a parameter control diagram
This can be done directly in the formula editing window or by right-clicking on blank space in the dimensions, measures, or parameter shelf, doing that exposes the dialog window that is used to define the parameter as you see in below diagram.
Enter the name of the parameter as you want it to appear in the control that is placed on the desktop, and then define the data type. Parameters can be numbers (floating decimal point or integers), Strings, Boolean (true/ false) and Date or Date and Time values.
The allowable values section is where you define the variables that will contain the Parameter. In Below diagram there is a small list of Measure names defined. While it isn’t always desirable, I suggest that for this type of parameter you exactly copy the field names of the Measures. This will make formula creation easier in the next step. However, if you find that the performance of your parameter is not good, use numbers in a series (1,2,3…) as your value names in the parameter definition. It makes creating the formula in the next step a little more difficult; using numbers in the parameter definition will generally result in a more responsible parameter control. This is especially noticeable with larger data sets.
Notice that there is a Display As option. This is used to create a name alias that will appear instead of the actual field name. The options to the right of the List of Values section are not applicable in this example, but are useful for cases where you might be using the values from another Parameter Control or adding members of a particularly large set. To complete the formula definition, click OK and the parameter will appear on the Parameters shelf.
Expose the Parameter in the Workspace:
In order for users to access the Parameter Control, it needs to be placed on the desktop. To do this, right-click on the Parameter name appearing in the Parameters shelf and select Show Parameter Control. If you access the parameter now, nothing will happen because you haven’t used the control yet in a formula or in any other way in the Visualization. The next step is to use this parameter variable in a formula.
Create a Formula That Uses the Parameter Control:
In below diagram the Parameter Control is used to change the Measure being plotted in the Time series. This requires a formula that will link the String values defined in the parameter to measure field names in the data source. You can see the formula definition in below diagram .
Now the parameter variable comes into play. The formula logic associates the selected parameter string with the related field name. This is why it is a good idea to define the Parameter String names to exactly match the field names you want to associate. It just makes writing the formula easier. But keep in mind that if performance degrades, using sequentially-ordered numeric values in the parameter definition will result in the best performance.
Clicking OK adds the Calculated Value to the Measures shelf with the name Select Measure. It’s also a good idea to give your parameter name the same name as the related calculations, especially if you have many parameters defined in the worksheet. This just makes it easier to retrace your work at a later date if you need to modify the Parameter Control to add or delete items.
Use the Calculated Value in the View:
Dragging the Select Measures to the Row shelf will activate the Parameter Control. Each selection made in the parameter control will trigger changes in the Select Measure formula and will change the measure being displayed in the Time series.
Using a parameter in a formula diagram
The parameter can be used to create multiple-purpose visualizations. There are many different ways in which the advanced parameters can be used. Your imagination is the only limit. For more examples, go to tableau software’s website and search for parameters. You will find many different forum posts related to parameters and also some training videos