Skip to main content
Filter insights by date range

Learn how to apply the Date Range filter to your insights

Neli Ivanova avatar
Written by Neli Ivanova
Updated over a week ago


When viewing insights, it is useful to be able to display them in a particular timeframe.

EXAMPLE: If you are using the built-in Jira velocity insight that calculates the amount of completed stories per sprint, without the ability to specify a time period, you need to have separate insights for each period that you want statistics for. For example - last week velocity and last month velocity.

Quantive allows you to apply the Data Range filter. This way, you are able to filter one insight to display metrics for different periods, without the need to maintain multiple insights for the same metric.

The Data Range filter is a built-in filter. Its key is date_range and it has a set of predefined values. The filter is applied to most of the built-in insights out-of-the-box.
For more information, see Create filters for insightboards.

Following are two approaches to filter insight by a date range:

  • The first approach hardcodes the required time period in the insight.

  • The second approach uses the Date Range filter.

Approach 1: Apply a data range by hardcoding it in the insight

In the following example, you have a simple SQL insight that counts all users that were created during a certain date range.

You can count the leads for a specific time period by hardcoding the WHERE clause, like in the following example:

COUNT(1) AS users_count
FROM quantiveresultsusers u
WHERE u.datecreated @@ '%%date_range%%'

You can use the automatic UI to generate a quick UI for front-end.

Approach 2: Apply the Data Range filter to the insight

Instead of hardcoding the data range in the insight, you can use a filter to dynamically change the data range. This way, you do not need to create different insights for different time periods.
For more information, see Filter the output of insights.

You use the key of the parameter - data_range, and the gtmhub.DURING function.

Use the following sample query:

FROM salesforce_lead
isdeleted = FALSE
gtmhub.DURING(createddate, '%%date_range%%')

In the above sample:

  • data_range is the key of the built-in Data Range parameter.
    For more information, see Create filters for insightboards.

  • %%date_range%% is the SQL placeholder syntax that is replaced by the value selected in the insightboard filter.

  • gtmhub.DURING([column_name], [time_period_definition_string]) is an SQL function that evaluates to TRUE or FALSE, depending on the specified parameters. In combination with the WHERE clause, it filters the result, so that only leads that are created in the specified time period are displayed.

Predefined values of the Data Range parameter

The gtmhub.DURING function accepts the following parameters:



all time

all_time, alltime


this_day, day, this day

this quarter

this_quarter, quarter, q, this_q, this q

this week

this_week, week

this month

this_month, month

this year

this_year, year

previous day

last_day, yesterday, previous_day, last day

last quarter

last_quarter, last_q, last q

last week


last month


last year



next_day, next day

next quarter

next_quarter, next_q, next q

next week


next month


next year


Filtering by custom date range

If you would like to include a custom range in your code, you just need to type in the column that you would like to filter by and then put the 2 values that are defining your range in a string separated by a "|" symbol.

For example in the following code, we are filtering by results that have been created in the declared timeframe.

FROM salesforce_lead

*This scenario is also very useful in a case that you would like to filter by a particular date.

Did this answer your question?