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

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.

Did this answer your question?