Custom Date Range Filter
Sometimes, users would like to apply their own data range filters on top of Periscope Data's default Date Range filter. This is useful for cases where there are multiple data columns for a dataset, and different filtering criteria should be applied to each.
The default date range filter has 2 options: either select a pre-determined interval (ex: 1 day, 7 days, 90 days) or enter your own start and end date. We've outlined how to replicate each of these options below:
Option 1: Show a list of intervals
Create a new filter and assign names and values for your desired intervals
Select "Match an expression for this filter" and enter the following SQL. Note you would have to tailor this slightly if your intervals differ slightly from the above!
case when [value] = '365' then [column] between getdate()::date - interval '364 day' and getdate()::date + interval '1 day' when [value] = '180' then [column] between getdate()::date - interval '179 day' and getdate()::date + interval '1 day' when [value] = '90' then [column] between getdate()::date - interval '89 day' and getdate()::date + interval '1 day' when [value] = 'mtd' then [column] >= date_trunc('month', (getdate())::timestamp)::date when [value] = 'ytd' then [column] >= date_trunc('year', (getdate())::timestamp)::date else 1=1 end
Option 2: Allow the User to Select any Start and End Date
In this case, we need to create 2 filters - one for start date and one for end date. Both will have a generated a list of dates for the user to select from. This would be a comprehensive list of all dates in the dataset.
For both the start and end date filters: In "Get names and values from the database," we want to run the following query on any table with a large number of rows (we derive the dates using the row_number window function)
select ( getdate()::date - row_number() over (order by true) )::date as n from table_with_many_rows order by 1 desc
Then select "Match an expression for this filter"
For the start date filter, use the following expression
For the end date filter, use the following expression
The final result will look like the screenshot below. To quickly call a certain date, the user can begin typing in their desired date in the open text field at the top of the filter instead of scrolling through all the options!
I wanted to do something slightly different, which was to create custom date choices that also offered a fallback to daterange. Here is what I came up with! All SQL is Redshift-flavored. Note that I've simplified slightly here to offer 2 Week Rolling, Custom Date Range, and Current MTD choices. The filter SQL:
select '2 Week Rolling' as name, '2_week_rolling' as value union select 'Custom Date Range', 'usedaterange' union select to_char(sysdate, 'Mon YYYY') || ' MTD', 'mtd'
It doesn't have to be SQL here; you can enter manual choices. I did it this way because mine also uses a calendar to get other months. The more complex version is:
with filter as ( select to_char(sysdate, 'Mon YYYY') || ' MTD' as name, 'mtd' as value, 3 as order_by_me union select '2 Week Rolling' as name, '2_week_rolling' as value, 1 as order_by_me union select distinct month_abbv || ' ' || date_year as name, to_char(date_trunc('month', date_id), 'YYYY-mm-dd') as value, 12000 + date_diff('month', date_id, '2018-01-01') as order_by_me from public.calendar where date_id >= '2018-01-01' and date_id < date_trunc('month', sysdate) union select 'Custom Date Range' as name, 'usedaterange' as value, 2 as order_by_me ) select value as id, name from filter order by order_by_me asc
Then my filter expression match is:
case when ( [value] = 'usedaterange' ) then ( [[column]=daterange] ) when ( [value] = 'mtd' ) then ( date_trunc('month', [column]) = date_trunc('month', sysdate) ) when ( [value] = '2_week_rolling' ) then ( [column]::date between sysdate::date - interval '14 day' and sysdate::date - interval '1 day' ) else ( to_char(date_trunc('month', [column]), 'YYYY-mm-dd') = [value] ) end
Now a user can select a business domain-friendly range of dates, or Custom Date Range and use the Date Range filter to select any other ranges. I hope this helps!Reply
These solutions are all _ok_... But, a far better solution would be for Periscope to provide the option to have a second (or third etc) of their date filters. Presumably that isn't difficult. I want to be able to select data from one period of time relating to (say) all users acquired at a different point in time.
Please Periscope make this happen.Reply