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

[column]>=[value]

For the end date filter, use the following expression

[column]<=[value]

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!

  

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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 Like 1
  • 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 Like
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 9 mths ago
      • Reported - view

      James Corbishley Definitely agree that this would be a great addition to the product to aid customer analytics! I've provided this feedback to our product team. For the immediate term, is there a functionality of date range filters that isn't covered in the above post? 

      Reply Like
    • Neha Kumar Hi Neha - yes, the above solutions do provide a solution. But, it is far from elegant. Periscope's big selling point is to make the access and visualisation easy and presented in a professional way. The single date filter that is standard does that.

      The above solutions are far from that.

      At best I see that I would need 6 filters to allow for any arbitrary date range to be selected if I want to use radio buttons to minimise input errors, or one (or two to make it easier) filters if I want to allow people to freely enter in date ranges (without having the calendar visulationsion option). With some people using mm-dd-yyyy and others dd-mm-yyyy, this option isn't great either. Thus, these are far from elegant professional solutions that I want to offer the consumers of my dashboards.

      Please, give us the option of having additional (good looking, easy to use) date filters. I provided this feedback more than 6 months ago and never heard anything further from Periscope. Hopefully this time something will come about...

      Reply Like
    • James Corbishley fixing type: *visualisation

      Reply Like
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 9 mths ago
      • Reported - view

      James Corbishley Definitely see your case here and understand that built-in additional date range filters will be the most elegant solution here. I have forwarded your feedback to our product team and we will definitely take your comments into consideration when developing our roadmap. I'll be following up with you over email shortly as well - let me know if you don't see our message come through!

      Reply Like
Like6 Follow
  • 6 Likes
  • 9 mths agoLast active
  • 6Replies
  • 3899Views
  • 3 Following