Control Elements & Data Manipulation

Control Elements

Workbooks support three element types: data elements, UI elements, and control elements.

Control elements and data elements work hand-in-hand to get the data you want in front of you. Data elements display your data; whereas control elements are built and used to manipulated that displayed data. Think data filtering and parameterization.

How do controls work?

Control elements can be added to a workbook to manipulate that workbook's data based on user input.

Filter Data with Controls

Each filter-based control element has three primary components that define the control: type, settings and target.

Control element type: A few examples of control types include lists, date ranges, and number ranges. 

Settings: Settings are defined by workbook editors and vary depending on control type. For example, lists require source data but date ranges do not.

Together, a control's type and settings define how that control displays on the page and what user input value(s) it will accept. 

Targets: Targets are the data elements that a control element points to.  When a control's input is changed, the data in its target(s) reacts accordingly. 

Parameters

Parameters are excellent for What-If Analysis and User Input Analysis.

A parameter is a customizable field that can be added to a workbook and referenced in formulas. This allows you to dynamically replace constant values used in calculations across your workbook. 

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. 

What does this mean for me?

If you are authoring or editing a workbook with controls, you should have a basic understanding of how controls are constructed and how they impact targeted data elements. 

Types of Controls

Filter Types

A control's type dictates the type of user input values that will be accepted by the control and the format in which those values are accepted. For example, an “include” control provides a list of data values for you to choose from, while a “range” control requests minimum and/or maximum values. 

Include

• Description: Selected values will be included in your data. All other other values will be excluded.
• Input Type: List of selectable values
• Column Types: Text, Numbers, Dates
• Maximum Number of Values Displayed: 200
• Sort Options: Descending by count (default), Ascending by count, Descending by alpha-numeric, Ascending by alpha-numeric

Note: Superficially, include and exclude filters look almost identical. A control element type can be identified from its vertical ••• menu, the editor panel, or from use and observation.

Exclude

• Description: Selected values will be excluded in your data.
• Input Type: List of selectable values
• Column Types: Text, Numbers, Dates
• Maximum Number of Values Displayed: 200
• Sort Options: Descending by count (default), Ascending by count, Descending by alpha-numeric, Ascending by alpha-numeric

Note: Superficially, include and exclude controls look almost identical. A control element type can be identified from its vertical ••• menu, the editor panel, or from use and observation.

Range (Number)

• Description: Only values within the specified range will be included in your data. The range is min/max inclusive
• Input Type: Min/Max numeric input boxes
• Column Type: Numbers

Date Range

• Description: Only values within the specified range will be included in the data. The range is min/max inclusive. Both fixed and relative date types are supported.
• Input Type: A single input box with the option to select fixed and/or relative dates for both min and max values
• Column Type: Dates

Text Match

• Description: Allows the user to search for full and partial matches between your input text and your data’s values.
• Input Types:
• (1) A list of formulas to match on (ie Contains, Starts with, Ends with, Like), in addition to their value excluding counter parts (ie Does not contain, Does not start with, Does not end with, and Not like)
• (2) A text input box for search text
• Column Type: Text

Top N

• Description: Ranks and limits data in the column based on your specifications.
• Input Type:
• (1) A list of rank order/direction (e.g. First N)
• (2) A numeric input for number of values to include
• Column Types: Text, Numbers, Dates

Switch

• Description: Allows the user to switch between True and False values.
• Input Types: Switch toggle
• Column Type: Boolean (true/false)

Drill Down

• Description: Allows the user to view pre-defined layers of data.
• Input Types: clickable text drill path
• Drill Category Column Types: Text, Numbers, Dates, Boolean (true/false)

Parameter Types

Textbox

• Description: Allows the user to enter a custom text or number value.
• Input Type: A single text box. The input value will be interpreted as either text or a number depending on its pre-defined value type.

List

• Description:  Allows for selection of a single value from the available list of values. The value list is defined either manually (by its creator) or from a source column in your workbook's data.
• Input Type: A list with radio buttons

Date picker

• Description: Allows for selection of a single date to use as a parameter value
• Input Type: A calendar date picker

Switch

• Description: Allows the user to switch between True and False values
• Input Type: Switch toggle

Create a Control

Before you start: This action is only available in Edit mode.

1. In the editor panel's sidebar, click the + icon. This will open the ADD NEW panel.
2. Under CONTROL ELEMENTS select your desired control element type. Your new element will appear on the page.
3. Select a Control type: Filter or Parameter. 
4. Use the editor panel to configure the control. 

Convert an Existing Element Filter to a Control

The Convert to page control option creates a copy of the existing data element filter. The original element is automatically added as a target to the new control. You can customize and add additional targets to the control via the editor panel.

When a control is created from an element filter, the existing filter remain accessible. Changing the input value of the filter update the input value in the control and vice versa.  

Before you start: This action is only available in Edit mode.

1. Click the element's filter button to open its filter popup.
2. Open filter's more menu.
3. Select Convert to page control.

    4. Use the editor panel to customize and add targets to your control. 

Use a Parameter in a Formula

Before you start: This action is only available in Edit mode.

1. Create your parameter.

    2. Create and select a new column.

    3. Click to focus the formula bar.

    4. Type in your formula, formatting the parameter id in brackets (similar to columns).

   5. Hit Enter on your keyboard, to save your formula.

   6. Once the formula is saved, changing the parameter value will automatically trigger the calculated column to re-calculate.

Workbook Parameters

A parameter is a customizable field that can be added to a workbook and referenced in formulas. They allow you to dynamically replace constant values used in calculations across your workbook. 

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. 

Create a Parameter

Before you start: This action is only available in Edit mode.

1. In the editor panel's sidebar, click the + icon. This will open the ADD NEW panel.
2. Under CONTROL ELEMENTS select your desired control element type. Your new element will appear on the page.
3. Under Control type select Parameter. 

   4. Use the editor panel to configure the parameter's Settings. 

Reference a Parameter in a Formula

Before you start: This action is only available in Edit mode.

1. Create a parameter.

     2. Add a new column.

     3. With the column selected, type your formula into the formula bar.

The parameter id should be placed inside square brackets (e.g. [My-First-Parameter]). 

4. Hit Enter on your keyboard, to save your formula.

5. Once the formula is saved, changing the parameter value will automatically trigger the calculated column to re-calculate.

Advanced Parameters

Data Source Parameters

Data source parameters are parameters that live on the workbook's data source but receive input from a parameter on the child workbook. 

Pass a Value to a Data Source Parameter

Data source parameters are accessible from the Source tab on a control's workbook editor panel view.

Before you start: This action is only available in Edit mode.

1. Create a new workbook parameter or select an existing one.
2. In the editor panel under Settings, select a Value type to pass to your data source's parameter. The value type must match the data source parameters value type.

     3. Open the workbook parameter's Source tab.

     4. Click + Add Source. Then select your data source.

Note: Only data sources containing one or more parameter(s) that accepts your selected value type will be available.

        5. If the data source has more than one parameter, use the dropdown menu to select your target parameter.

EXAMPLE

In the following example, [My Parameter] accepts a number value and passes it to a data source's parameter.

The table to the right of [My Parameter] is powered by the same data source. Its column, [+ 5 column], is calculated from the data source. The formula for [+ 5 column] adds 5 to the data source parameter: 

[data-source-parameter] + 5

When a value is input into [My Parameter], it is passed to the data source column and [+ 5 column] is recalculated with that value. 

0 + 5 = 5

3 + 5 = 8

10 + 5 = 15

Data Element Filters

Data element filters allow you to limit your data to show only that which meets certain criteria. Filters are created directly from a single element.

Once created, they can be converted into page controls.

Filter Types

A filter’s type dictates the type of user input values that will be accepted by the filter and the format in which those values are accepted. For example, an include filter provides a list of data values for you to choose from, while a range filter requests minimum and/or maximum values. 

MFour Studio workbooks support seven filter types. The filter types you can choose from for each given filter are dependent on the type of column the filter targets. 

Note: Json columns are the only column type that cannot be filtered. To filter on json data, you will first need to extract data from the json object.

Include

• Description: Selected values will be included in your data. All other other values will be excluded.
• Input Type: List of selectable values
• Column Types: Text, Numbers, Dates
• Maximum Number of Values Displayed: 200
• Sort Options: Descending by count (default), Ascending by count, Descending by alpha-numeric, Ascending by alpha-numeric

Note: Superficially, include and exclude filters look almost identical. A filter's type can be identified from its "more" menu or from use and observation.

Exclude

• Description: Selected values will be excluded in your data.
• Input Type: List of selectable values
• Column Types: Text, Numbers, Dates
• Maximum Number of Values Displayed: 200
• Sort Options: Descending by count (default), Ascending by count, Descending by alpha-numeric, Ascending by alpha-numeric

Note: Superficially, include and exclude filters look almost identical. A filter's type can be identified from its "more" menu or from use and observation.

Range (Number)

• Description: Only values within the specified range will be included in your data. The range is min/max inclusive.
• Input Type: Min/Max numeric input boxes
• Column Type: Numbers

Date Range

• Description: Only values within the specified range will be included in your data. The range is min/max inclusive. Both fixed and relative date types are supported.
• Input Type: A single input box with the option to select fixed and/or relative dates for both min and max values.
• Column Type: Dates

Text Match

• Description: Search for full and partial matches between your input text and your data’s values.
• Input Types:
• (1) A list of formulas to match on (ie Contains, Starts with, Ends with, Like), in addition to their value excluding counter parts (ie Does not contain, Does not start with, Does not end with, and Not like).
• (2) A text input box for search text
• (3) A checkbox for selecting case sensitivity (available through the filter's more menu.)
• Column Type: Text

Boolean (true/false)

• Description: Filters on true, false and null
• Input Type: A list of values
• Column Type: Logical (true/false)

Top N

• Description: Ranks and limits data in the column based on your specifications.
• Input Type:
• (1) A list of rank order/direction (e.g. First N)
• (2) A numeric input for number of values to include
• Column Types: Text, Numbers, Dates

Create a Filter

Before you start: This action is only available in edit mode.

1. Select the element you want to filter.
2. Hover over the column you would like to filter, and click its caret (▼) icon button. 
3. Click Filter.

    4. Your new filter will be added to the filter list and displayed in the filter popup above the element.

                1. Existing filters can be accessed from filter button in the element's inline toolbar.

                2. If the element is expanded, the same button is available in the page toolbar.

      5. A filter type is auto-selected based on the column's type.

                1. Open the individual filter's more menu to change its type.

Keep Only or Exclude (Quick Filter)

Before you start: This action is only available in edit mode.

To quickly include or exclude a single value from a column's data, use the Keep only or Exclude options from the cell's right-click menu. 

Example 1: Right click on a vertical bar chart's bar to open the cell context menu for the x-axis value represented by that bar. In the image below, clicking Keep only 2018 would filter the visualization to only show that one bar. Clicking Exclude 2018 would filter out the 2018 bar, leaving the 2015, 2016, 2017 and 2019 bars.

Example 2: Right click on a table or pivot table cell to open that cell's context menu. In the image below, clicking Keep only Midwest would filter the table to only show data rows with a cell value matching "Midwest" in the [Store Region] column. Clicking Exclude Midwest would filter out any row with a cell value matching "Midwest" in the [Store Region] column.

Open & Edit an Existing Filter

1. If you have a full workbook page open, hover over the element and click the filter button in its inline toolbar.

OR

If the element is expanded, the same button is available in the page toolbar.

2. Use the element's input box(es) to modify a filter's value(s).

Edit a Filter Type

Before you start: This action is only available in edit mode.

1. Click the element's filter button to open its filter popup open the filter popup.
2. Click the filter's more menu
3. Select the new filter type.

Disable a Filter

1. Click the element's filter button to open its filter popup open the filter popup.
2. Click filter's switch toggle to disable or reenable a filter. 

Explore Visualization Drill Paths

Workbooks' Drill Anywhere feature provides an interactive method for digging into visualized data. It is supported for most visualization types.

This feature was defined with ad-hoc data exploration, and is only available to users with Can Edit or Can Explore access to the workbook.  If you're creating a workbook with a broader target audience or want to use the same drill path across multiple target visualizations, you should instead create a drill down control.

Drill Anywhere

1. Right click on a value on your visualization to open its context menu.

In this example, we open the context menu for the bar chart's 2017 [Year] column.

      2. Click Drill down... to open the Drill down modal.

     3. Click a column to drill into it.

In this example, we drill into [Store Region].

Drilling into [Store Region] for the [Year] 2019, adds a new date filter and switches the X-AXIS from [Year] to [Store Region]. We now see the sum of sales by region for the year 2019.

Because this visualization is in its maximized view, you can also see its underlying data table structure update accordingly. Instead of being grouped by [Year], the data is now grouped by [Store Region].

Customize a Visualization's Drill Down Column List

Before you start: This action is only available in Edit mode.

1. Right click on a value on your visualization to open its context menu.
2. Click Drill down to open the Drill down modal.

     3. Click Edit List. 

     4. Use the checkboxes to select only the columns you want available.

     5. Click Save.