How to Work with Date Range SQL Parameters in Sigma Computing

A practical example of using Date Range Control Elements in SQL queries as parameters

George Pipis

--

Sigma Computing is a cloud analytics platform that uses a familiar spreadsheet interface to give business users instant access to explore and get insights from their cloud data warehouse. In this tutorial, I will share with you my experience with Date Range parameters that I found really challenging.

Sigma has a collection of “Control Elements” such as “TEXT BOX”, “LIST VALUES”, “SLIDER”, “RANGE SLIDER”, “DATE”, “SWITCH”, “DRILL DOWN” and “TOP N”

These elements can be used as filters or as SQL parameters for the dashboards. In this tutorial we will focus on the Date range control type.

As we can see, we should add the Control ID, where in my case is mydaterange. Then, this Control ID will be used in the SQL as a parameter. Finally, there is the Control label which is the label that will appear in the dashboard, where in my case is Date Range. As you can see below, we have created the control element called Date Range and we have passed a start and an end date.

Keep in mind that apart from the Between there are other options such as "On", "Before", "After" and so on.

Date Range SQL Parameters in Sigma

The Control Element that we have created has to interact with our data. We can achieve that by running an SQL query using the Control Element as a parameter. Keep in mind that the Control ID in our case is mydaterange and it will be passed in the SQL query.

For exhibition purposes, let’s run a simple query where we would like to filter some event logs based on a timestamp. For example:

SELECT event_ts, user_id, event_type, variant_id
FROM mytable
WHERE event_ts BETWEEN coalesce(CAST((SELECT date_range.start FROM (SELECT {{mydaterange}} date_range)) as timestamp), dateadd(day, -100, getdate())) AND coalesce(cast((SELECT date_range.end FROM (SELECT {{mydaterange}} date_range)) as timestamp)…

--

--

George Pipis

Sr. Director, Data Scientist @ Persado | Co-founder of the Data Science blog: https://predictivehacks.com/