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), getdate())
Let’s explain the above query. First, we need to mention that this query is for Redshift, since the syntax is different between databases. The Sigma parameter should be enclosed in double curly brackets passing the Control-ID, i.e. {{<Control-ID>}}, where in our case is {{mydaterange}}.
The challenging part is how to get the start and end date from the date range object. In Redshift this is done by running:
-- the start date
cast((SELECT date_range.start FROM (SELECT {{mydaterange}} date_range)) as timestamp)
-- the end date
cast((SELECT date_range.end FROM (SELECT {{mydaterange}} date_range)) as timestamp)
However, this is not enough since we need to pass a value when the end-user has not specified a start or an end date, for example when he/she passes the After, or the Before. In this case, we need to add the coalesce() function. In my example, if the end date is null, I pass the current date and when the start date is null, I pass the current date minus 1000 days.
Finally, I would like to mention that this example works in Redshift, but the syntax is different in other Databases. For example:
Snowflake
-- VARIANT parameter
to_timestamp({{Date-Range-Control}}[‘start’]) or to_timestamp({{Date-Range-Control}}:start)
to_timestamp({{Date-Range-Control}}[‘end’]) or to_timestamp({{Date-Range-Control}}:end)
BigQuery
-- STRUCT parameter
{{Date-Range-Control}}.start
{{Date-Range-Control}}.end
Postgres
-- JSONB parameter
({{Date-Range-Control}}->>‘start’)::timestamptz
({{Date-Range-Control}}->>‘end’)::timestamptz
References
- [1] Sigma Community
- [2] Special thanks to Brett Bedevian
