Filter insights by date range | Quantive Help Center

Overview

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:

SELECT 
 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:

SELECT COUNT(*)
FROM salesforce_lead
WHERE
    isdeleted = FALSE
  AND
    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:

Parameter	Alternatives
`all time`	`all_time`, `alltime`
`today`	`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_week`
`last month`	`last_month`
`last year`	`last_year`
`tomorrow`	`next_day`, `next day`
`next quarter`	`next_quarter`, `next_q`, `next q`
`next week`	`next_week`
`next month`	`next_month`
`next year`	`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.

SELECT COUNT(*)
FROM salesforce_lead
WHERE
   gtmhub.DURING(createddate,
   '2022-08-10T00:00:00.000Z|2022-08-10T23:59:59.000Z')

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