Dynamic Date Filter Comparisons


(lara.tromba) #1

## Use Case:

You want to compare two arbitrary time periods, and more specifically want to be able to display this as a single value comparison.

In other words, you want to be able to take advantage of Looker’s native date filtering capabilities, but also dynamically update the single value tiles so that if a user updates the date filter to “in the past 90 days” the single value tile will compare the past 90 days to the prior 90 days.

##How We Do It:

At a high level, we’ll be grabbing the start date and end dates of the time period as entered by the end user in the date filter. We use these to calculate the interval between the dates, subtract this interval from the start date (or you can think of it as the end date of the previous period), and obtain the previous start date. These dates then allow us to create a “timeframe” field for ‘period’ and ‘previous period’, which can be used to set up the single value comparison.

If you’re not familiar with creating single value visualization comparisons, it might be worth revisiting before implementing this pattern.

##Try it Yourself

Because of the use of date fields, the construction of this pattern can vary based on the SQL dialect being used. In the examples below we use Redshift and MySQL.

First, we create a date filter that we’ll add to a dashboard or look, which will enable an end user to enter a date or date range.

  filter: date_filter {
   description: "Use this date filter in combination with the timeframes dimension for dynamic date filtering"
    type: date
  }

Next, we grab the start and end dates as entered by the user in the date filter. We use a syntax that was first described here.

In order to account for the possibility that an end user selects “is before” in the date filter, we wrap this in a CASE statement that will make the start date ‘1970-01-01’ (unix epoch but you can make this the first date in your db, etc.). The same is done when we grab the end date of the filter, to account for the user selecting “is on or after” (where is the end date is the current date).

Grab the start date of the filter:

  dimension_group: filter_start_date {
    type: time
    timeframes: [raw]
    sql: CASE WHEN {% date_start date_filter %} IS NULL THEN '1970-01-01' ELSE NULLIF({% date_start date_filter %}, 0)::timestamp END;;
# MySQL: CASE WHEN {% date_start date_filter %} IS NULL THEN '1970-01-01' ELSE  TIMESTAMP(NULLIF({% date_start date_filter %}, 0)) END;;
  }

Grab the end date of the filter:

  dimension_group: filter_end_date {
    type: time
    timeframes: [raw]
    sql: CASE WHEN {% date_end date_filter %} IS NULL THEN CURRENT_DATE ELSE NULLIF({% date_end date_filter %}, 0)::timestamp END;;
# MySQL: CASE WHEN {% date_end date_filter %} IS NULL THEN NOW() ELSE TIMESTAMP(NULLIF({% date_end date_filter %}, 0)) END;;
  }

Note that we make these raw, which allows for flexibility in the interval calculation and also ensures these fields won’t show up in the explore.

Once we have the start and end dates of the date filter, we calculate the difference between two dates to get the interval.

  dimension: interval {
    type: number
    sql: DATEDIFF(seconds, ${filter_start_date_raw}, ${filter_end_date_raw});;
# MySQL: TIMESTAMPDIFF(second, ${filter_end_date_raw}, ${filter_start_date_raw});;
  }

Then, we subtract this interval from the start date to get the start date of the previous period.

  dimension: previous_start_date {
    type: date
    sql: DATEADD(seconds, -${interval}, ${filter_start_date_raw}) ;;
# MySQL: DATE_ADD(${filter_start_date_raw}, interval ${interval} second) ;;
  }

Now that we have the previous period start date, the start date, and the end date, we can create a field with for the two timeframes we’re interested in – period and previous period. Whereas the “period” of interest is the time between the start and end date as entered by the user in the date filter, the previous period is the period between the previous start date and the “start date” (i.e. the “previous period” ends when the “period" begins). Here you’ll use a date field (like ${created_on_raw}) that exists within your model already.


  dimension: timeframes {
    description: "Use this field in combination with the date filter field for dynamic date filtering” 
    suggestions: ["period","previous period"]
    type: string
    case:  {
      when:  {
         sql: ${created_on_raw} BETWEEN ${filter_start_date_raw} AND  ${filter_end_date_raw};;
        label: "Period"
    }
    when: {
      sql: ${created_on_raw} BETWEEN ${previous_start_date} AND ${filter_start_date_raw} ;;
      label: "Previous Period"
    }
    else: "Not in time period"
  }
}

You can now create the look that you need using the timeframes field:

Make sure that if you have another date filter on the dashboard, the looks that are to be dynamically filtered are responding to the “date filter” above.


Dynamic filter not showing % on dashboard but in tile
(Sean Higgins) #3

Awesome pattern, Lara.

I adjusted for Big Query recently and the dialect differences in Standard SQL can make this a bit finnicky, so here’s the full code:

filter: date_filter {
    description: "Use this date filter in combination with the timeframes dimension for dynamic date filtering"
    type: date
  }

  dimension_group: filter_start_date {
    type: time
    timeframes: [raw,date]
    sql: CASE WHEN {% date_start date_filter %} IS NULL THEN '1970-01-01' ELSE CAST({% date_start date_filter %} AS DATE) END;;
  }

  dimension_group: filter_end_date {
    type: time
    timeframes: [raw,date]
    sql: CASE WHEN {% date_end date_filter %} IS NULL THEN CURRENT_DATE ELSE CAST({% date_end date_filter %} AS DATE) END;;
  }

  dimension: interval {
    type: number
    sql: DATE_DIFF(${filter_start_date_raw}, ${filter_end_date_raw}, DAY);;
  }

  dimension: previous_start_date {
    type: string
    sql: DATE_ADD(${filter_start_date_raw}, INTERVAL ${interval} DAY) ;;
  }


  dimension: is_current_period {
    type: yesno
    sql: ${order_date} >= ${filter_start_date_date} AND ${order_date} < ${filter_end_date_date} ;;
  }
  dimension: is_previous_period {
    type: yesno
    sql: ${order_date} >= ${previous_start_date} AND ${order_date} < ${filter_start_date_date} ;;
  }

  dimension: timeframes {
    description: "Use this field in combination with the date filter field for dynamic date filtering"
    suggestions: ["period","previous period"]
    type: string
    case:  {
      when:  {
        sql: ${is_current_period} = true;;
        label: "Selected Period"
      }
      when: {
        sql: ${is_previous_period} = true;;
        label: "Previous Period"
      }
      else: "Not in time period"
    }
  }

  measure: selected_period_order_revenue {
    type: sum
    sql: ${TABLE}.total_price ;;
    filters: {
      field: is_current_period
      value: "yes"
    }
    value_format_name: decimal_1
  }
  measure: previous_period_order_revenue {type: sum
    sql: ${TABLE}.total_price ;;
    filters: {
      field: is_previous_period
      value: "yes"
    }
    value_format_name: decimal_1
  }
}

(lara.tromba) #4

Thank you, @shiggins!!


(sami.rubenfeld) #5

Hi,

Adapted the block for redshift.

filter: date_filter { 
description: "Use this date filter in combination with the timeframes dimension for dynamic date filtering" 
type: date 
} 

dimension_group: filter_start_date { 
type: time 
timeframes: [raw,date] 
sql: CASE WHEN {% date_start date_filter %} IS NULL THEN '2013-01-01' ELSE CAST({% date_start date_filter %} AS DATE) END;; 
} 

dimension_group: filter_end_date { 
type: time 
timeframes: [raw,date] 
sql: CASE WHEN {% date_end date_filter %} IS NULL THEN CURRENT_DATE ELSE CAST({% date_end date_filter %} AS DATE) END;; 
} 

dimension: interval { 
type: number 
sql: datediff(day, ${filter_start_date_raw}, ${filter_end_date_raw});; 
} 

dimension: previous_start_date { 
type: string 
sql: DATEADD(day, - ${interval}, ${filter_start_date_raw});;  
} 

dimension: is_current_period { 
type: yesno 
sql: ${created_date} >= ${filter_start_date_date} AND ${created_date} < ${filter_end_date_date} ;; 
} 

dimension: is_previous_period { 
type: yesno 
sql: ${created_date} >= ${previous_start_date} AND ${created_date} < ${filter_start_date_date} ;; 
} 

dimension: timeframes { 
description: "Use this field in combination with the date filter field for dynamic date filtering" 
suggestions: ["period","previous period"] 
type: string 
case: { 
when: { 
sql: ${is_current_period} = true;; 
label: "Selected Period" 
} 
when: { 
sql: ${is_previous_period} = true;; 
label: "Previous Period" 
} 
else: "Not in time period" 
} 
} 

measure: selected_period_order_revenue { 
type: sum 
sql: ${TABLE}.total_price ;; 
filters: { 
field: is_current_period 
value: "yes" 
} 
value_format_name: decimal_1 
} 
measure: previous_period_order_revenue { 
type: sum 
sql: ${TABLE}.total_price ;; 
filters: { 
field: is_previous_period 
value: "yes" 
} 
value_format_name: decimal_1 
} 


(DCL) #6

Awesome pattern here and thank you for the above contributions!

Just a few notes from working through this with a customer in case they help out:

  1. the previous two comments have additional measures (selected_period_order_revenue and previous_period_order_revenue) in them for calculating total order revenue per time frame based on a dimension called total_price, these are not required for the visualization in the main post

  2. though these are very useful if you have similar data, these measures require that you have an equivalent DIMENSION to total_price for whatever you are wanting to sum.