Switch between daily, weekly, monthly views with automatic autofill for missing grains

Looker Q&A Looker
1

There’s a topic here that shows how we can create a date_granularity parameter that allows us between daily, weekly, etc views of a measure.

There’s a problem with this that the resulting date dimension doesn’t autofill for dates that the measure is null.

Is there anyway to make this work?

Here’s how I’ve defined the dimensions:

  parameter: date_granularity {
    type: string
    allowed_value: { value: "Day" }
    allowed_value: { value: "Week" }
    allowed_value: { value: "Month" }
    allowed_value: { value: "Quarter" }
    allowed_value: { value: "Year" }
  }

  dimension: transaction_date_dynamic {
    label_from_parameter: date_granularity
    sql:
      case
        when {% parameter date_granularity %} = 'Day' then cast(${gmv_transaction_date} as string)
        when {% parameter date_granularity %} = 'Week' then cast(${gmv_transaction_week} as string)
        when {% parameter date_granularity %} = 'Month' then cast(${gmv_transaction_month} as string)
        when {% parameter date_granularity %} = 'Quarter' then cast(${gmv_transaction_quarter} as string)
        when {% parameter date_granularity %} = 'Year' then cast(${gmv_transaction_year} as string)
      end
    ;;
  }