Date Range Filters

👤 This documentation outlines how to apply the default date range filter into Periscope charts, and is intended for SQL Users. View Only Users can select values within the date range filter to update the timeframes displayed in any charts where the filter is implemented.

Dashboard-Level Date Range

To use the built-in Date Range filter at the top of the dashboard, write:

where [my_datetime_column=daterange]

For example, New Users Per Day can be modified applying the dashboard-wide Date Range filter like so:

The Date Range filter can be updated at the dashboard level, for example, to "90 days." Our options include 1, 3, 7, 14, 30, 60, 90, 180, 365, and All Days. We also allow for Current Month, Current Week, Last Week, and a Custom Range. Note that Current Week and Last Week both use Monday as the start of the week per the international standard - ISO 8601.

The filter parameters will then be applied to all charts using the Date Range filter.

The user can then re-enter the chart editor and click the Query tab to display the SQL showing the date restriction for the 90-day dashboard-level filter, complete with timezone correction:

To filter on only the start or end of the Date Range, write:

where my_datetime_column >= [daterange_start] 
where my_datetime_column < [daterange_end]

Additional Date Range Filters

Users are able to create additional date range filters to use on a dashboard instead of relying exclusively on the default Periscope Date Range filter. This will allow users to compare date ranges, as well as create date range filters with custom names.

To create a new date range filter open the filter pane at the top of the dashboard. Then, click the "+" button to the right of the dashboard-wide filter dropdown.

Select "Create as date range filter" and then enter a title.
*Note*: The custom filter cannot be named daterange, date_range, or aggregation.

The new custom date range filter will then appear alongside the dashboard filters.

The additional date range filter can be used in a chart with the same syntax as the standard Date Range filter:

where [my_datetime_column=customdaterange] 

Timezone Conversions

To remove any backend timezone conversions that convert timestamps without timezones to the site timezone, write:

where [my_datetime_column=daterange_no_tz] 

More detail on the timezone conversions in Periscope, refer to the documentation here.

Ad-Hoc Date Ranges

To create chart-specific date range filters that are independent of the dashboard-level filters, write:

where [my_datetime_column=30days]

Any number of days will work between the "=" sign and the word "days".

Yesterday, Week-To-Date, Month-To-Date and Year-To-Date

To filter date ranges to yesterday or the current calendar week, month, or year, write:

where [my_datetime_column:wtd]

where [my_datetime_column:mtd]

The built-in date range and aggregation filters syntax are similar.

Looking for More?

Try our community page for guidance on topics like: 

Our support team is ready to help