Data Expert @ iKnowlogy Ltd.
Tableau is a great BI tool to publish dashboard, but for some reason, still after so many years of being a top tool, their date filter widget is not that great. Creating a date widget tool that satisfies the end user is often a hassle.
That is why we decided to solve this once and for all and implement a Google-Analytics like filter and dates comparison widget.
Our solution is based Snowflake’s UDTF’s (user-defined-table-functions), but you can implement it in any database.
TL;DR
This post shows how to implement a date filter widget in Tableau that gives you the following capabilities:
- Filter dates using quick pre-selected date ranges or a custom date range.
- Toggle comparison between date ranges and even overlay metrics on a common dates x-axis.
Step 1 - A DWH
If your company already has one, great.
If not, and you are reading this blog article, you probably need one.
DWH’s used to be complicated and expensive, but nowadays they are not.
We almost always recommend Snowflake. Will write more about this in a different article.
The Date Widget Requirements
Below you can see a screen shot from Google Analytics.
We wanted to copy some of these capabilities into our dashboards –
- Be able to easily filter a predefined date range (yesterday / last week / MTD / etc..) or a custom date range.
- Easily toggle a comparison to a corresponding previous period.
- Be able to overlay the 2 date ranges on a daily graph.
One UDTF to Rule Them All
Snowflake UDTF’s are functions that return tables. They accept input parameters. They can be written in either SQL or Javascript.
We decided to manage all the logic for our date filter parameters with a Snowflake UDTF. You can find the SQL script of the function at the end of this article. Notice the documentation within the script.
The function accepts 6 parameters and returns a table that we join with the dataset that we want to filter or analyze. Let’s understand what this function returns from these 3 examples:
Notice that the result tables that return from the function contain a row per date for the dates in the “Current” range and the “Previous” range.
The “DAY_IN_RANGE” and “CURRENT_DATE_LABEL” columns will allow us to overlay the 2 ranges one on top of the other.
Adding the Widget to Tableau
1Add 6 parameters to Tableau that match the UDTF input arguments.
2Define a custom SQL in your data source and insert the Tableau parameters in their corresponding place.
select a.*,
b.DATE_RANGE,
b.DAY_IN_RANGE,
b.CURRENT_DATE_LABEL::date as CURRENT_DATE_LABEL
from PUBLIC.TIMESERIES_FACT_1 a
inner join table(IKNOWLOGY.PUBLIC.GET_DATES(
<Parameters.Date range>,
<Parameters.From Date>,
<Parameters.To Date>,
<Parameters.Compare to>,
<Parameters.Previous From Date>,
<Parameters.Previous To Date>
)) b
on a.dt=b.dt::date
3Create your Tableau Dashboard
Here are a few screen shots from the result with the widget functionality.
Notice how the parameter selections control that the displayed date range.
The date widget now allows easy selection of one of the following:
• Custom date range
• Today
• Yesterday
• Last week
• Last month
• Last 7 Days
• Last 28 Days
• MTD
• WTD
• YTD
The UDTF creation Script
create or replace function IKNOWLOGY.PUBLIC.GET_DATES
(
_date_range varchar,
_from_date date,
_to_date date,
_compare_to varchar,
_prev_from_date date,
_prev_to_date date
)
returns table (
dt TIMESTAMP_NTZ(9),
date_range varchar,
day_in_range integer,
current_date_label TIMESTAMP_NTZ(9)
)
as
$$
/*
This funtion return 2 date ranges based on the following input parameters:
_date_range:
------------
- Mandatory
- The later date range for the comparison
- Possible Values: Custom,
Today,
Yesterday,
Last week,
Last month,
Last 7 Days,
Last 28 Days,
MTD,
WTD,
YTD
_from_date & _to_date:
---------------------
- Optional. Ignored unless _date_range='Custom'
- The start and end date of a custom date range that you want selected.
_compare_to:
-----------
- Mandatory
- The previous date range that you want to compare to.
Possible values: None,
Custom,
Previous period
_prev_from_date & _prev_to_date:
---------------------
- Optional. Ignored unless _date_range='Custom'
- The start and end date of a custom date range that you want selected.
Columns in output table:
-----------------------
- dt - dates in range
- date_range = Current or Previous
- day_in_range = day sequence in each date_range
- current_date_label = a column that you can use as a X-axis when comparing dates
*/
with raw_calendar as
(
select dateadd(day,seq4(), '2018-01-01') as dt
from table(generator(rowcount => 10000))
where dt <= date_trunc('month',dateadd('month',1,current_date))
),
current_range as (
select
dt,
'Current' as status,
row_number() over (order by dt) day_in_range
from raw_calendar
where
(_date_range = 'Custom' and dt >= _from_date and dt <= _to_date)
OR
(_date_range = 'Today' and dt = current_date)
OR
(_date_range = 'Yesterday' and dt = dateadd('day',-1,current_date))
OR
(_date_range = 'Last week' and date_trunc('week',dt) = date_trunc('week',dateadd('week',-1,current_date)))
OR
(_date_range = 'Last month' and date_trunc('month',dt) = date_trunc('month',dateadd('month',-1,current_date)))
OR
(_date_range = 'Last 7 Days' and dt >= dateadd('day',-7,current_date) and dt < current_date)
OR
(_date_range = 'Last 28 Days' and dt >= dateadd('day',-28,current_date) and dt < current_date)
OR
(_date_range = 'MTD' and date_trunc('month',dt) = date_trunc('month',current_date) and dt < current_date)
OR
(_date_range = 'WTD' and date_trunc('week',dt) = date_trunc('week',current_date) and dt < current_date)
OR
(_date_range = 'YTD' and date_trunc('year',dt) = date_trunc('year',current_date) and dt < current_date)
),
previous_range as (
select
dt,
'Previous' as status,
row_number() over (order by dt) day_in_range
from raw_calendar
where
_compare_to <> 'None'
AND (
(_compare_to = 'Custom' and dt >= _prev_from_date and dt <= _prev_to_date)
OR
(_compare_to = 'Previous period' and _date_range = 'Custom' and datediff('day',_from_date,_to_date) >= 6 and dt < _from_date and dt >= dateadd('day',-datediff('day',_from_date,_to_date)-1,_from_date)) -- eq or above seven days range
OR
(_compare_to = 'Previous period' and _date_range = 'Custom' and datediff('day',_from_date,_to_date) < 6 and dt >= dateadd('day',-7,_from_date) and dt <= dateadd('day',-7,_to_date)) -- below seven days, remains same week days
OR
(_compare_to = 'Previous period' and _date_range = 'Today' and dt = dateadd('day',-1,current_date))
OR
(_compare_to = 'Previous period' and _date_range = 'Yesterday' and dt = dateadd('day',-2,current_date))
OR
(_compare_to = 'Previous period' and _date_range = 'Last week' and date_trunc('week',dt) = date_trunc('week',dateadd('week',-2,current_date)))
OR
(_compare_to = 'Previous period' and _date_range = 'Last month' and date_trunc('month',dt) = date_trunc('month',dateadd('month',-2,current_date)))
OR
(_compare_to = 'Previous period' and _date_range = 'Last 7 Days' and dt >= dateadd('day',-7*2,current_date) and dt < dateadd('day',-7,current_date))
OR
(_compare_to = 'Previous period' and _date_range = 'Last 28 Days' and dt >= dateadd('day',-28*2,current_date) and dt < dateadd('day',-28,current_date))
OR
(_compare_to = 'Previous period' and _date_range = 'MTD' and date_trunc('month',dt) = date_trunc('month',dateadd('month',-1,current_date)) and day(dt) < day(current_date))
OR
(_compare_to = 'Previous period' and _date_range = 'WTD' and date_trunc('week',dt) = date_trunc('week',dateadd('week',-1,current_date)) and dayofweekiso(dt) < dayofweekiso(current_date) )
OR
(_compare_to = 'Previous period' and _date_range = 'YTD' and date_trunc('year',dt) = date_trunc('year',dateadd('year',-1,current_date)) and dayofyear(dt) < dayofyear(current_date))
)
),
unioned as
(
select
dt,
status as date_range,
day_in_range
from current_range
union all
select
dt,
status as date_range,
day_in_range
from previous_range
)
select a.*,
max(dt) over (partition by day_in_range order by dt desc) as current_date_label
from unioned a
$$
;