Custom Date Range Filter

Update: Periscope Data now allows you the ability to create additional date range filters without having to go through the below workaround. Refer to our docs here under "Additional Date Range Filters"


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!

  

9replies 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
      • 1 yr 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
      • 1 yr 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
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 3 mths ago
      • 1
      • Reported - view

      James Corbishley Wanted to drop a note here to let you know that we now have a built in way to create additional date range features. Check out our docs here, under "Additional Date Range Filters"

      Reply Like 1
    • Neha Kumar thank you!!!!!!!

      Reply Like
  • We use QuickBooks and I've always liked their built-in data range selections such as Current MTD, Current YTD, Prior Fiscal Year, Prior Fiscal YTD, etc.  (See attached image).  So I tried to emulate this in Periscope.  I wanted this to work in conjunction with the standard datarange filter. 

    I set-up a "type in names and values" filter in Periscope called "Period" using the same period groupings/names from Quickbook.  

    Then I created a Parameter Snippet (see below and attachment) called [date_range(period)].  I send the text string of the filter value ('[Period]') into the parameter snippet which returns a single string value of two dates separated with a "/" as in [startdate]/[enddate] based on the selected period range. 

    For example, if today is May 20, 2019, and my selected period range filter is "Current-MTD", the resulting string from the parameter snippet is "2019-05-01/2019-05-20"

    If the new Period filter is blank, then use the standard datarange filter.  Otherwise, use the Period filter.

    In the query, use this Case When statement:

    Case when '[Period|Default]' = 'Default' then --This phrase tests if the [Period] filter is blank using the "|default" value of "default"
        [Tran_Date=daterange_no_tz]  -- if the new Period filter is blank, use the standard datarange 
        else Tran_Date between to_timestamp(left([date_range('[Period]')],10),'YYYY-MM-DD') and to_timestamp(right([date_range('[period]')],10),'YYYY-MM-DD') end 
    -- This parses the date string into the start date (left 10 positions of the parameter snippet) and end date (right 10 positions) and converts the string to a date using "to_timestamp" function.

     

    date_range(period) Parameter Snippet (also see attached Word document to easily replicate my snippet

    Note: There is a separate WHEN statement for each value (in bold below) created in the Period filter (of course, the bullets don't go in your SQL)

    case

    • when lower([period])='current_ytd' then to_char(date_trunc('year',[current_date:est]),'YYYY-MM-DD')+'/'+to_char(date_trunc('day',[current_date:est]),'YYYY-MM-DD')
    • when lower([period])='prior_ytd' then to_char(dateadd('year',-1,date_trunc('year',[current_date:est])),'YYYY-MM-DD')+'/'+to_char(dateadd('year',-1,date_trunc('day',[current_date:est])),'YYYY-MM-DD')
    • when lower([period])='full_prior_year' then to_char(dateadd('year',-1,date_trunc('year',[current_date:est])),'YYYY-MM-DD')+'/'+to_char(date_trunc('year',[current_date:est])-1,'YYYY-MM-DD')
    • when lower([period])='current_month_current_year_mtd' then to_char(date_trunc('month',[current_date:est]),'YYYY-MM-DD')+'/'+to_char(current_date,'YYYY-MM-DD')
    • when lower([period])='current_month_prior_year_mtd' then to_char(dateadd('year',-1,date_trunc('month',[current_date:est])),'YYYY-MM-DD')+'/'+to_char(dateadd('year',-1,[current_date:est]),'YYYY-MM-DD')
    • when lower([period])='prior_month_mtd' then to_char(dateadd('month',-1,date_trunc('month',[current_date:est])),'YYYY-MM-DD')+'/'+to_char(dateadd('month',-1,[current_date:est]),'YYYY-MM-DD')
    • when lower([period])='prior_month_current_year_full_month' then to_char(dateadd('month',-1,date_trunc('month',[current_date:est])),'YYYY-MM-DD')+'/'+to_char(date_trunc('month',[current_date:est])-1,'YYYY-MM-DD')
    • when lower([period])='prior_month_prior_year_mtd' then to_char(dateadd('year',-1,dateadd('month',-1,date_trunc('month',[current_date:est]))),'YYYY-MM-DD')+'/'+to_char(dateadd('year',-1,dateadd('month',-1,[current_date:est])),'YYYY-MM-DD')
    • when lower([period])='prior_month_prior_year_full_month' then to_char(dateadd('year',-1,dateadd('month',-1,date_trunc('month',[current_date:est]))),'YYYY-MM-DD')+'/'+to_char(dateadd('year',-1,date_trunc('month',[current_date:est])-1),'YYYY-MM-DD')

    else
    Null
    end

    Reply Like
Like6 Follow
  • 6 Likes
  • 2 mths agoLast active
  • 9Replies
  • 4667Views
  • 4 Following