Determining the number of days in a filter range (Community)

View the original community article here

Last tested: May 7, 2019
 

Sometimes users want to know the number of days that are in the date range filtered on in the Explore. This is particularly useful for performing calculations (in table calcs or in the sql parameter of a dimension or measure).

To dynamically get this value, we can use the date_start and date_end liquid variables, which are described in more detail here in the liquid variables doc. We can use these variables to determine the filter values applied on the date dimension, and then use SQL to determine the number of days between the start and end of the filter.

In this example, we're using MySQL which uses DATEDIFF, but this can be adapted to fit any specific SQL dialect.

dimension: num_days {

type: number

sql: DATEDIFF({% date_end created_date %}, {% date_start created_date %}) ;;

}

In this hypothetical example, we have a dimension group called created. We're pulling in the start and end values for the filter applied to this dimension, and setting our new num_days dimension equal to that result! Using this approach, the dimension will then represent the number of days in the filtered range.

Another way we can accomplish this is through the use of the dimension of type duration. For example, we can create a filter and a dimension like so:
 

filter: date_filter {

type: date

}

dimension: diff_day_filter {

type: duration

sql_start: {% date_start date_filter %}

sql_end: {% date_end date_filter %}