This QuickStart is part of a series of QuickStarts designed to instruct new users on how to use the variety of controls Sigma offers.
Control elements (controls) and data elements work hand-in-hand in Sigma.
Data elements display the data (ie: tables or visualizations), and controls manipulate the displayed data by filtering and/or using parameters.
Controls are basically filters, and sometimes the terminology is interchangeable.
The line gets blurred more since controls can also be used as parameters.
To read more about creating flexible reports with parameters, see here.
If you already completed QuickStart fundamentals 1-5, you have already used controls.
In Fundamentals 2 - Working with Tables, we created a filter directly from a table column (Store Region), and converted that filter into a page control. This control is an example of a List control
.
In Fundamentals 4: Working with Pivot Tables v2, we used a Segmented control
to present a different filtering experience.
Selecting the correct control is often more art than science, as there many controls to choose from.
In general, if using the control is obvious to the user, requires the minimum number of clicks, and produces the desired results, it is a good choice.
Since we have already used a few controls, we will focus on areas of interest that have not been covered, but are important to understand as you build in Sigma.
We will be working with some common sales data from our fictitious company Plugs Electronics
, reusing content we created in the QuickStart Fundamentals 1: Getting Around.
Sigma combines the unlimited power of the cloud data warehouse with the familiar feel of a spreadsheet, with no limit on the amount of data you wish to analyze. Sigma is awesome for users of Excel and even better for customers who have millions of rows of data.
The typical audience for this QuickStart includes users of Excel, common Business Intelligence or Reporting tools, and semi-technical users who want to try out or learn Sigma. Everything is done in a browser, so you already know how to use that. No SQL or technical skills are needed to do this QuickStart.
Open the Fundamentals
workbook created in earlier fundamentals QuickStarts.
Add a new page and rename it to Controls
.
Add a child
table to the page, based on the PLUGS_DATA
table on the DATA
page. We can do this in just a few mouse clicks; how slick is that!
Clicking the +
icon in the element panel, we can see that there are several different controls available:
For the controls we haven't used yet, we will provide examples. Don't feel pressured to build each one; simply following along is perfectly fine.
Use this QuickStart as a guide for the controls that interest you, and refer to the others as needed in the future.
The process for adding the control to the canvas is the same, regardless which control is selected, so we will skip that part and just show what each configured control looks like.
The slider control enables users to select a single value from a continuous range by sliding a handle along a track.
Lets say we want to see who are big spenders are, by filtering Sales
to display high values.
First, it would be nice to know the min and max values in our 4.5M row table.
Open the Sales
columns menu and select Column details
to see:
Now we can see we need to set the maximum value for Sales
higher than $21,712.50.
Lets use 30,000
to be safe.
Add a new Slider
control to the page:
Configure the control as:
Set the Target
of the control to Controls
> PLUGS_DATA
:
Slide the control to around 20k. These customers might make a good list for a marketing campaign:
However, big spend does not always mean big profit. We should evaluate for both to refine our marketing target list.
Sigma makes it really simple to add copies of existing controls, saving you time
Lets say we want to also get all the rows sold at high margin, amongst the list of "big spenders".
Open the slider control's menu and click Duplicate
:
We use the slider to allow the user to quickly return all the orders with a profit greater than (or equal to) the maximum value configured:
Drag the control to be side-by-side with the Sales
control.
Modify the target for the new control targets the PLUGS_DATA
table > Profit
column and rename the control to Profit
:
We checked and found there are some orders sold a big losses, so we set the minimum value to -10,000
for the Profit
slider.
Also set the Control ID
for Profit
to profit-slider
.
Now adjust the Profit
slider to be around $6500. These are are target high-value customers:
When creating controls, it is always a good idea to make sure the row counts are what is expected. For example, we just set our maximum sales value over the highest value in the table and no minimum.
We would expect the table to have the same row count as our un-filtered source table, and it is easy to compare the two, as a quick validation.
We want to ensure that we did not inadvertently create a filter that does not initially show all the rows, based on its configuration (unless that was intended).
Reset both controls to show all rows, and verify the total row count matches the total row count on the Data
page > PLUGS_DATA
source table.
Check that the values match:
It is recommended to rename the controls, so users understand them as best possible.
Also, reset the two sliders to show the full range of data for now:
Click Publish
.
This control is very similar to the slider, except that the user is able to adjust both the min and max values in the control itself.
For example, we might want to target customers who spent a fair modest amount of money on computers, to see if we can sell them upgrades or accessories.
We can easily filter for computers:
Add a Range Slider
control, configure it, set the target to the Sales
column, rename it to Sales Hi-Low Range
and then adjust it as:
We now have a target list for our campaign.
Reset the range slider control to show all rows (don't forget to clear the Product Type
filter on computers) and click Publish
:
Configuring a basic date control is done in just a few clicks, giving it a target of DATA
> PLUGS_DATA
> DATE
and a Control ID
:
The default for the date control is Between
two dates:
Opening the date options menu exposes all the other selection criteria available to the user, including a custom option:
The date control's options make it really simple to get to the data that you need quickly:
Reset both controls to show all rows, and click Publish
:
If you have done any of the other sections, you know how to make the Top N
control work.
Here is what that might look like, targeting the Profit
column:
The Top N
control provides the user the ability to select from four different groupings of values, as shown in the image above, #2.
The Switch control
2 in Sigma allows users to toggle between True
and False
values, and then filter for matching records.
It is a simple "on/off" toggle that can be used to dynamically adjust the rows within a dashboard, based on a binary choice.
Lets create a simple example, based on filtering our PLUGS_DATA
by transaction type.
Imagine Plugs users want to analyze sales transactions, differentiating between Purchase
and Returns
.
By using the switch control, users can easily toggle between Purchase
and Returns
, within a dashboard. From there, users can drill further into the data to gain the insights that interest them.
A problem we notice right away is that our PLUGS_DATA
table is missing the transaction type
column; we need to add that.
Sigma is flexible, and the decision of where to add this column has some implications.
We could add it independently of PLUGS_DATA
, so that other tables in the workbook do not have access to this column. However, this column ‘feels useful', so we want all the other "child elements" of PLUGS_DATA
to be able to use it if desired.
Since this column does not exist anywhere in our workbook, we can't use a Sigma Lookup
, but we can create a join to it, as it does exist in a related table in the warehouse.
Navigate to the Data
page, select the PLUGS_DATA
table and open its menu.
Click Element source
and Edit join
:
Using the source selector, search for F_Sales
and click to select it:
Notice that Sigma makes table suggestions to save time. In this case, we searched and selected the F_SALES
table.
Sigma suggests that we might want to join on matching ORDER_NUMBER
columns. We don't have to accept that, but since it is correct, we click to select that join condition and then click Select
:
The next step shows us the current join configuration, allowing us to make adjustments, add more sources and the results.
In this case, there are less matched records than total records, which indicates that some transactions do not have a matching record. This is because our sample data does not have order numbers for cash sales transactions. That is fine; we can ignore those for now.
Click Preview Output
:
In the next page, we want to only select the Transaction Type
column.
Collapse the PLUGS_ELECTRON....
(#1 in image below), deselect all columns from F_SALES
(#2 in image below), and check the box for Transaction Type
.
Click Done
:
The column is added to the last position on the PLUGS_DATA
table. We moved it to first column for the screenshot:
If we want to see the distribution of purchase to online transactions, we can look at column details:
We can see the row counts for purchase and return. Now we know what to expect when we enable our switch control.
It is handy to know ahead of time what the row counts will be, so we know switch is working right when we implement that.
Close
the details modal.
The last step is to expose the new column on the Controls
page.
Open the PLUGS_DATA
table column control on the Controls
page and locate the Transaction Type
column.
Check the box on so, that the column is made visible:
Drag the column to be the first column in our table.
Add a Switch
control to the page, rename it Transaction Type
and set it's Control ID
to switch-example
:
We can now toggle the switch control on and off, but the table is "aware" of it yet.
We need to make the table respond to this controls value, as it is changed.
Add a new column next to Transaction Type
, rename it to Switch
and set its formula to:
If([switch-example] = True, If([Transaction Type] = "Purchase", True, False), If([Transaction Type] = "Return", True, False))
When [switch-example] is TRUE:
When [switch-example] is FALSE:
The last step is to add a table filter on the Switch
column, so that it responds to changes of the control.
Be sure to select only the True value, as that is what the control should be set to while we create this filter:
Now we can toggle between Purchase
and Return
transactions:
Hide the Switch
column, as users do not need to see that:
Click Publish
.
A Synced control
is different than a Duplicate
copy.
For example, you may want to track your organization's high-level sales data on one page and drill down into region-specific data on another.
If the same filter control targets elements on both pages, we recommend that you display the control on both pages; this enables you and your workbook's viewers to edit the control value from either page, instead of switching back and forth.
Add a new page and rename it to Synced Controls
.
Add a Table
to the page, using the PLUGS_DATA
table on the DATA
page as the source.
Add a new List control
to the page and set it to use the PLUGS_DATA
> Region
column.
Set the Control ID
to Synced-Region
.
Now Create a synced copy
from the Synced-Region
control:
Now the second control can be moved to another page in the workbook, and be set to target some other element.
There are a few use cases where this functionality can be useful:
1: Two controls, one target.
2: Maintaining context across pages.
3: Preselecting control values for users.
To read more about synced controls, see here.
One of the "ah-ha" moments, is when users realize that controls can be created based on a formula too.
Lets explore that concept.
Add a new page and rename it to Formulas
.
Add a Table
to the page, using the PLUGS_DATA
table on the DATA
page as the source.
For this exercise, lets reduce the available data down so that refresh is as fast as possible.
Filter the Date
column to the current 1 year only
, to reduce the data to around 600k records.
Add a List control
to the page and configure as:
Make sure the Control ID
is set to Stack-By
; we will reference that in our formula, so syntax matters.
Add a new column to the table, in the first position. Rename it to Stack By
and set it's formula to:
Switch([Stack-By], "State", [Store State], "Store Region", [Store Region], "Brand", [Brand])
This formula will change the Stack By
columns value, based on the selection made in the control.
As a last step, we can Group
the table by Stack By
. How slick is this!
Click Publish
.
In this QuickStart, we covered the the variety of the more popular controls provided by Sigma. We explored using them to manipulate sample data and demonstrated just how easy it can be to solve business problems.
For more information on Sigma controls, see here.
The next QuickStart in this series covers administration in Sigma.
Additional Resource Links
Be sure to check out all the latest developments at Sigma's First Friday Feature page!
Help Center Home
Sigma Community
Sigma Blog