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 toTRUE
orFALSE
, depending on the specified parameters. In combination with theWHERE
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.