date filter using last x complete days in option relative_timeframes

[pp_smec]

Hi.

I have a dashboard which uses a date-filter and my users want to use a simplified datepicker with pre-defined options (e.g. Last 7 Days) so we went for relative_timeframes option.

Showing: 

The problem with this seems to be that this uses as standard "is in the last 7 Days" as filter. But what I want is "is in the last 7 complete days" like i could select if I would switch to advanced:

Is there a possibility to make "is in the last 7 complete days" work for the relative_timeframes date filter?

[jcdufault · Accepted solution]

Looker's handling of dates, while getting better, has always been a bit frustrating. So you essentially have 2 options.

1. Teach your users how to use the default date filter options.
2. Write custom parameter/filters/dimensions

Option 2 is more work but then you can really control how things work (but are bound to maintaining it).

You could do something like:

parameter: number_of_days {
    label: "Number of Days"
    type: unquoted
    allowed_value: {
      label: "Next 7 Days"
      value: "7"
    }
    allowed_value: {
      label: "Next 14 Days"
      value: "14"
    }
    allowed_value: {
      label: "Next 30 Days"
      value: "30"
    }
    allowed_value: {
      label: "Next 60 Days"
      value: "60"
    }
    allowed_value: {
      label: "Next 90 Days"
      value: "90"
    }
    allowed_value: {
      label: "Next 120 Days"
      value: "120"
    }
    allowed_value: {
      label: "Next 180 Days"
      value: "180"
    }
    allowed_value: {
      label: "Next 240 Days"
      value: "240"
    }
    allowed_value: {
      label: "Next 300 Days"
      value: "300"
    }
    allowed_value: {
      label: "Next 365 Days"
      value: "365"
    }
    default_value: "30"
  }

## In your view declaration you would swap to:
## You will need to change the where clause logic to whatever you need
view: daily_revenue_and_occupancy {
  #sql_table_name: schema.table_name #remove this and swap to derived_table
  derived_table: {
    sql: select * from schema.table 
         {% if number_of_days._in_query %}
         where date_trunc(day,${date_column}) < DATEADD(day,{% parameter number_of_days %}+1, current_date {% endif %} ;;
}
#... your dimensions and measures

 Option 3 is to use a Period over Period product that is maintained by the community like this one.