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.

Gtmhub 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 leads from an external system Salesforce.

If you have the following SQL query:

SELECT COUNT(*)
FROM salesforce_lead
WHERE isdeleted = FALSE

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

SELECT COUNT(*)
FROM salesforce_lead
WHERE
isdeleted = FALSE
AND
createddate > now() - INTERVAL '1 week'

You can then rename the insight to Leads generated last week.

Using this approach, you cannot reuse the algorithm of the insight to count the lead for another period.


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

Did this answer your question?