Date Parameters with No Data

Looker Q&A Looker
, ,
1

Hi!

I am trying to visualize all the calendar dates of 2025 so that way the end user could see which days didn’t bring in any metrics such as revenue. Looker is linked to BQ and if there isn;’t data for one day, it automatically ignores any missing calendar dates due to it simply not existing. I was able to create a derived calendar view in Looker and join my data to that, however, it makes the date filter: daily, weekly, monthly, etc. no longer applicable.

Anyone come across a similar situation and have a hack to make it work for both?

2 Likes
2

Your calendar-derived table is the right move. The reason your date filters broke is likely that your explore is no longer based on a view that has the main dimension_group for time.

Here’s the “hack” to make it work for both.

The Solution: dimension_group on the Calendar View

The trick is to make your new calendar view the base view of your explore and define a dimension_group on it. Looker’s date filters (daily, weekly, monthly) need to attach to a dimension_group of type: time.

Step 1: Your Calendar View File

Create a new view file (e.g., calendar.view.lkml) for your derived table. The key is adding the dimension_group to this view.

LookML

# calendar.view.lkml
view: calendar {
  derived_table: {
    sql: SELECT cast(calendar_day AS DATE) AS date
         FROM UNNEST(
           GENERATE_DATE_ARRAY('2025-01-01', '2025-12-31', INTERVAL 1 DAY)
         ) AS calendar_day
         ;;
  }

  # THIS IS THE CRITICAL PART
  dimension_group: date {
    type: time
    timeframes: [date, week, month, quarter, year]
    sql: ${TABLE}.date ;;
  }
}

Step 2: Your Explore

Now, in your model file, set up the explore starting with the calendar view and then left_outer joining your revenue data to it.

LookML

# your_model.model.lkml
explore: calendar {
  label: "Revenue Report"

  # LEFT OUTER JOIN is essential
  # It keeps ALL dates from the calendar, even if no revenue exists
  join: your_revenue_view {
    type: left_outer
    relationship: one_to_many # or one_to_one, depending on your revenue data
    sql_on: ${calendar.date} = ${your_revenue_view.created_date} ;;
  }
}

Step 3: Your Revenue View (Small Tweak)

To make your visualizations clean (showing 0 instead of NULL for days with no revenue), you should update your revenue measure.

LookML

# your_revenue_view.view.lkml
view: your_revenue_view {
  ...

  dimension_group: created {
    type: time
    ...
    sql: ${TABLE}.created_at ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${TABLE}.revenue_amount ;;
    # Use COALESCE to turn NULLs (from the left join) into 0s
    value_format_name: usd_0
    sql: COALESCE(${TABLE}.revenue_amount, 0) ;;
  }
}

Note: I’m assuming your revenue table has a dimension_group called created that you’re joining on. Just use whatever your date field is.

How This Works

  1. Base View: The explore is now based on calendar, which has a complete list of 2025 dates.

  2. Date Filter: When you use the date filter in the Explore UI, it now filters the calendar.date dimension group, which works perfectly (e.g., “Daily”, “Weekly”).

  3. Left Join: The left_outer join guarantees all those filtered calendar dates are kept.

  4. Data Fill: Your your_revenue_view data is tacked on where it matches. For dates with no revenue, the total_revenue measure will be NULL.

  5. COALESCE: The COALESCE(..., 0) in your measure turns those NULLs into 0s, making your charts and tables look correct.

This gives you the best of both worlds: a complete date axis and fully functional Looker date filters.

1 Like