Using two string fields to filter a date range dynamically

[ryanmjennings]

Hi everyone, so this one is a doozy… I’ve been running up against some serious limitations with the built in date filtering in Looker and have gone down quite a rabbit hole trying to fix things. First let me describe the use case and challenges. I have a set of dashboards that are being served to pass-through customers who are mostly non-technical. Because of this everything needs to be very intuitive and have as few “quirks” as possible. We are also using a Redshift backend, and because of this, the stock timeframe filtering (last 30 days, current month, etc) generates a GETDATE function in the WHERE clause. This causes Redshift to recompile the SQL every single day because GETDATE evaluates to a new value each day, slowing down the queries by a significant amount (think going from 1 second to 12 seconds). So issue number one is:

• We are unable to use the built in timeframe filtering because of how Redshift’s compiler handles GETDATE

Due to this, I have tried switching from timeframe filtering to a simple date range filter, but this brings up the issue of what the default value is set to. With a date range filter, the default value has to be two actual days and cannot be dynamic. This would mean that we either have to set the date range manually and go in to change it at a regular interval, or set it to some very wide value to capture all of the data. The first method is a lot of extra work and the second method could result in very large queries depending on the customer’s data. Issue number two is:

• We cannot use a date range filter because the default value cannot be dynamic and would require manual intervention

Another issue with a date filter is that its values cannot be updated by another filter. An idea I had was possibly having a button toggle that switches between a static “most recent data” mode and “historical data” mode that could be filtered by date. Because the date filter’s values cannot be limited by this toggle, the user would be able to select conflicting filters and error out the dashboard (e.g. they select “most recent data” and a date range that is outside the range baked into the toggle).

So what I’ve settled on is using two date filters, one to filter the start date of the date range, and one to filter the end date. These filters are both string filters as well, so that the values can be updated by switching the “data mode” toggle. However, I’m really not sure how to get these to “combine” to filter the model down to the range between the two dates. I’ve tried building these filters as parameters and piping them into sql_always_where at the explore level, I’ve tried making them as LookML filters and doing the same, but nothing seems to work correctly. So here is the behavior that I need:

• One date mode toggle that switches between a static “most recent data” mode and a date-filterable “historical data” mode
• Two filters, one for start date and one for end date, that are both type: string, whose values update based on the state of the data mode toggle above
• If there is no value in either date filter, the dashboard displays all dates

And here is some of the LookML I’ve written to try to get this to work:

Mode Toggle

  parameter: date_range_param {
    label: "Date Range"
    default_value: "yes"
    allowed_value: {
      label: "Most Recent"
      value: "yes"
    }
    allowed_value: {
      label: "Historical"
      value: "no"
    }
  }

Filters

 filter: min_date_filter {
    type: string
    suggest_dimension: min_current_date
  }

  filter: max_date_filter {
    type: string
    suggest_dimension: max_current_date
  }

Date Dimensions

dimension: min_current_date {
    group_label: "Dynamic Date [TEST]"
    type: string
    sql:
      CASE WHEN {% parameter date_range_param %} = 'yes' AND ${recent_dates} IS NOT NULL THEN ${recent_dates}
           WHEN {% parameter date_range_param %} = 'no' THEN ${full_date}
           ELSE null
      END
    ;;
  }

  dimension: max_current_date {
    group_label: "Dynamic Date [TEST]"
    type: string
    sql:
      CASE WHEN {% parameter date_range_param %} = 'yes' AND ${recent_dates} IS NOT NULL THEN ${recent_dates}
           WHEN {% parameter date_range_param %} = 'no' THEN ${full_date}
           ELSE null
      END ;;
  }

Explore-Level sql_always_where

  sql_always_where: ${dim_date.full_date} BETWEEN {% parameter dim_date.min_date_filter %} AND {% parameter dim_date.max_date_filter %} ;;

Really any ideas at this point are welcome. I feel like I’ve worked myself into a rabbit hole and this doesn’t feel like a great solution to the problems I outlined, but I cant think of anything else. Thanks in advance for the help!

[ryanmjennings · Accepted solution]

Okay, in case anyone else has this specific issue, I did find a solution. Here are the modifications I made to my code to make this work:

Filters

 parameter: min_date_param {
    type: string
    suggest_dimension: dynamic_date
    full_suggestions: yes
  }

  parameter: max_date_param {
    type: string
    suggest_dimension: dynamic_date
    full_suggestions: yes
  }

Date Dimensions

  dimension: min_date_dim {
    type: date
    sql: CASE WHEN {% parameter min_date_param %} = '' THEN NULL ELSE {% parameter min_date_param %} END ;;
  }

  dimension: max_date_dim {
    type: date
    sql: CASE WHEN {% parameter max_date_param %} = '' THEN NULL ELSE {% parameter max_date_param %} END ;;
    }

  dimension: dynamic_date {
    group_label: "Dynamic Date [TEST]"
    type: string
    sql:
      CASE WHEN {% parameter date_range_param %} = 'yes' AND ${recent_dates} IS NOT NULL THEN ${recent_dates}
           WHEN {% parameter date_range_param %} = 'no' THEN ${full_date}
           ELSE null
      END
    ;;
  }

Explore-Level sql_always_where

  sql_always_where: CASE WHEN (${dim_date.min_date_dim} IS NOT NULL AND ${dim_date.max_date_dim} IS NOT NULL) THEN ${dim_date.full_date} BETWEEN ${dim_date.min_date_dim} AND ${dim_date.max_date_dim} ELSE ${dim_date.full_date} = ${dim_date.dynamic_date} END ;;