Parameters

Use parameters to make worksheets and dashboards more interactive. Parameters are used to dynamically replace values used in calculations. Parameters are always exposed on the worksheets they are defined for, and can be exposed on any dashboard that references the worksheet.

Click here to see how parameters work on a live dashboard.

What-If analysis and User Input Analysis are made easier using parameters. Viewers can change the value of the parameter, changing the value everywhere that the parameter is used.

Parameters can be Text, Number or a Date. They can be left as a box that accepts any input of the correct type, or can be defined in a list. 

How to Create a Parameter

  1. Click on the parameters tab on the top right of your worksheet.
  2. Click the + button.
  3. Define your parameter.

 

Reference a Parameter in a Formula

Parameters can be referenced by name in any formula. Simply type in the name of the parameter when writing your formula in the formula bar.

One way to use parameters is to take the place of constants. This is helpful when you want it to be obvious what value is used or when you want to be able to change the input value quickly. Parameters are helpful when performing “what if” analysis. What if sales grew by 7% instead of 5%? With parameters, you just have to change the 5 to a 7 in one place.

Filtering Based on Parameters

Setting up a filter based on a parameter requires two steps: creating a column with the filter conditions, filtering based on the results of the column.

Setting up a filter to only show customers that have spent more than X dollars takes just a few steps. First, set up a parameter with a number as the input. We’ll call it [Spend]. Next, determine how much each customer spent. Then, set up a column with the following formula.

[Customer Spend] >= [Spend]

Now, filter your new column to only show answers where the result is True. You can set whatever spend limits you like using the parameter, and the worksheet will re-filter. This parameter is always visible on the Worksheet, and can also be added to any dashboard built with the worksheet.

Add a Parameter to a Dashboard

When you add a visualization to a dashboard from a worksheet that uses parameters, you have the option to make the parameter visible on the dashboard.

Available parameters will automatically appear on the right-hand side of the screen when editing the dashboard. You can choose to show or hide each available parameter by clicking on the eye icon in the top right of each parameter box.

Click here to see how parameters work on a live dashboard.

Data Source Parameters

Parameters on your worksheet can pass values to the parameters on your worksheet's data sources. If a Dataset that is used as a source for your worksheet has parameters, you can set up your worksheet parameters to pass values to the source parameters. In this way, you can control parameters on your data source via parameters on your worksheet. 

Data source parameters are listed under your worksheet parameters. Expand the view of the parameter to see what the default value is, and to choose a worksheet parameter to serve as an input. A single worksheet parameter can affect several Dataset parameters. 

Screen_Shot_2019-12-19_at_4.56.46_PM.png

SQL Parameters

Parameters on your worksheet can pass values to your SQL data sources as well. Once a Parameter is created, you can reference the value of that parameter in your SQL source by wrapping the parameter’s name within curly brackets: {{<parameter name>}}. Note that SQL Parameters only support a limited set of special characters including: spaces (“ “), apostrophes (“‘“), underscores(“_”), and dashes (“-”).

After the parameter is added to your SQL, a change to the parameter value will propagate to your SQL and reload your worksheet with the new value in your SQL source.

For example, if you have a parameter named Birth State in your Worksheet, you can reference the value of that parameter using {{Birth State}} in your SQL source. When the SQL runs, it will use the value of the parameter in the worksheet:

mceclip0.png

Once you have added the {{Birth State}} parameter, you can use the worksheet’s parameter UI control to change the value that is passed to the SQL source:

2020-02-21_19.53.32.gif

Was this article helpful?

Anything else?

Our customer support team is here to help.

Contact Us