A parameter is a customizable field that can be added to a worksheet and referenced in formulas. Creating parameters in your worksheets, and referencing them in formula columns, allows you to dynamically replace values used in calculations across a worksheet’s lineage. You can go on to use these variable calculations in worksheet filters, pass them on to downstream dashboards, or use them to manipulate data source queries. 

Parameters can be huge assets for What-If Analysis and User Input Analysis. One common use case is implementing a parameter in place of a constant value. Using a parameter provides you the flexibility to change that value very quickly. For example, you may want to compare product sales growth by a variable 2%, 5%, and 10% percent. A parameter can be used to quickly track this variable value and inject it into formulas. 

Summary of Content

Requirements
Anatomy of a Parameter
      The Control Panel
      The Parameter List
      The Parameter
      The Parameter Modal
Creating a Parameter
      Create a Basic Parameter
      Create a Value List Parameter
Referencing a Parameter in a Formula
Filtering with Parameters
Using Parameters in Dashboards
Editing a Parameter
Deleting a Parameter
Advanced Parameters
      Data Source Parameters
      SQL Parameters
Parameter Permissions
Related Resources

Requirements

  • You must be a Sigma Creator or Admin to create, edit, and apply worksheet parameters.

Anatomy of a Parameter

The Control Panel

Parameters live in the worksheet control panel, located on the left side of every Sigma worksheet. This panel also includes any worksheet Totals and Filters. Learn more.

66_-_parameter_01.png

The Parameter List

All existing parameters are listed in the control panel under the header PARAMETERS. If the worksheet has any source parameters, they will be listed directly below the worksheet parameters in the control panel.
67_-_parameter_02.png

The Parameter

The following example outlines the basic structure of a parameter when displayed in the control panel.

(1)  Data type icon
This represents the value type of the parameter: text, number, or date.

(2)  Parameter name
The name you give to the parameter.

(3)  Parameter menu
Open this menu to edit, duplicate, or delete the parameter.

(4)  Parameter value input
This is where you can set the parameter’s value. The input type is dependent on the parameter’s settings. You may see a list of values, a text input, a numeric input, or a date input. 
68_-_parameter_1234.png

The Parameter Modal

The parameter modal allows you to create and edit worksheet parameters. Modal field options vary depending on the Data Type and Suggested Value fields.
69_-_add_parameter_modal.png

Suggested Values: ‘All’ vs ‘List’

The parameter modal’s Suggested Values field determines the parameter’s input type.

All

Depending on the Data Type, a text, number, or date input will be available on the parameter. In the control panel, any value matching the selected value type can be set as the parameter value. Learn how to create this type of parameter.

List

The ‘List’ option allows you to define a custom set of value options for the parameter. In the control panel, any value in the list can be selected as the parameter value. No other values are accepted when using a parameter list in a worksheet*. Learn how to create this type of parameter.

*Please Note: This is not the case when worksheet parameters are used on dashboards OR when worksheets pass values to Data Source Parameters. In both cases, it is possible to pass any value to the parameter. As such, value lists SHOULD NOT be considered a security measure. 

Creating a Parameter

Create a Basic Parameter

  1. If the control panel is not already open, open it by clicking either of the two "Show Controls" buttons.
    53_-_show-controls.png

  2. Click on the blue + icon on the right side of the panel’s PARAMETERS section.
    70_-_add_parameter_from_control_panel.png

  3. The Add Parameter modal will now appear on your screen.

  4. Name the parameter.
    71a_-_add_parameter_modal.png

  5. [optional] Add a description.

  6. Select the Data Type that you would like to use for the parameter. This can be text, number, or date.
    In this example, we will select ‘Number’.
    71b_-_add_parameter_modal_2.png

  7. Under Suggested Values, select ‘All’ or ‘List’.
    ‘All’ allows the parameter to accept any input value of your previously-selected data type. Alternatively, ‘List’ provides you with the option to create a custom list of optional values.  Learn more.
    In this example, we will use the default ‘All’ option. To instead learn how ‘List’ works, skip down to the Create a Value List Parameter instructions.

  8. [optional] Specify a parameter Default Value.

  9. [optional] Select the Format of the parameter’s value.

  10. Click Save to save your new parameter.

  11. Your new parameter will now appear in the worksheet control panel. From here you can quickly input any value.
    Now that you have a parameter, it is time to reference it in a formula.

Create a Value List Parameter

  1. Follow steps (1) through (6) under Create a Basic Parameter.

  2. Under Suggested Values, select ‘List’.
    71c_-_add_parameter_modal_3.png

  3. [optional] Select the Format of the parameter’s value.
    In this example, let’s select ‘Currency’.

  4. Under Values List, add each value you would like to use as the parameter value. Display values will be automatically pre-filled based on your previously-specified Format field.
    71d_-_add_parameter_modal_4.png

  5. [optional] After creating your value list, go back up to the Default Value field, and select a default value.

  6. Click Save to save your new parameter.

  7. Your new parameter will now appear in the worksheet control panel. From here you can quickly input any value.
    Now that you have a parameter, it is time to reference it in a formula.

Reference a Parameter in a Formula

Parameters can be referenced by their name in any column formula. The formula will automatically rerun, updating your data, whenever you enter a new parameter value.
To reference a parameter in a formula, type the name of the parameter into the formula bar, using square brackets [ ].
72a_-_use_parameter_fx_bar.png

To demonstrate this, let’s reuse the parameter from the above Create a Basic Parameter instructions to calculate a new column’s value.

  1. Create a new column in the worksheet.

  2. Type a formula into the formula bar.
    In this example, our formula will check if each table row’s [Sales Amount] is greater than our [Min Sales Amount] parameter. (ie `[Sales Amount] > [Min Sales Amount]`)

  3. Hit ‘Enter’ on your keyboard to calculate the value in the formula editor.

  4. This will calculate a True/False (logical) column.
    The formulated column will automatically update when you enter a new parameter value.
    72b_-_use_parameter_in_formula.png

Filter with Parameters

To filter your data based on a parameter, you will need to (1) create a parameter, (2) reference it in a column’s formula, and (3) create a filter for that column.

To demonstrate this, let’s reuse the parameter from the above Create a Basic Parameter instructions and the column from the Referencing a Parameter in a Formula instructions, to create a new filter.

  1. To start, you will need our previously created parameter and the column referencing it.
    72b_-_use_parameter_in_formula.png

  2. Open the column’s menu, and select Add Filter....

  3. The filter modal will appear prompting you to create a filter.
    Customize your filter and/or hit Save to continue.
    72c_-_add_parameter_filter.png

  4. The filter will appear in the control panel under FILTERS.
    You can now update both the parameter and the new filter directly from the control panel to focus your data!
    72c_-_use_parameter_filter.png

Delete a Parameter

The following instructions will permanently delete your parameter.

  1. Open the control panel.

  2. In the PARAMETERS list, hover over the parameter you would like to delete, and click the caret icon to open the dropdown menu.

  3. Click Delete.

Parameter Permissions

  • If you are a Sigma Creator or Admin, you can view, create, edit, and delete parameters on worksheets and Datasets you have access to. However, as with other document changes, you must have edit permissions on the document to publish your work. If you have view-only access, you may save your document changes as a new copy. Learn more about permissions.
  • If you are a Sigma Viewer, you have access to view, but not edit Dataset worksheet parameters.
  • All Sigma users can input values into parameters made accessible from a workbook.