How to Generate dates not in a dataset

[jamesnestler]

Problem: You want to generate a list of all dates between x_startdate and y_enddate but do not have those dates in your base dataset. This is common in many analytics contexts; for example, if you'd like to see sum/count of sales by day/date part, while also seeing days/date parts where there were no sales.

Solutions:

• Dimension Fill: A built in feature of Looker that fills in dates

There's little downside to this feature, however it only works when one and only one dimension is selected in the explore, and there are no filters applied to measures.

Doc: https://docs.looker.com/exploring-data/visualizing-query-results#filling_in_missing_dates_and_values​

• Write SQL that generates dates: Will be dialect specific

Some dialects, like BigQuery, have a GENERATE function, while for others you may need to write out equivalent SQL manually. This SQL logic would need to be written in a derived table, and from there you would use that derived table view as the base table of subsequent explores.Two Examples:

Bigquery

SELECT day

FROM UNNEST(

GENERATE_DATE_ARRAY(DATE('2015-06-01'), CURRENT_DATE(), INTERVAL 1 DAY)

) AS day

Source: https://stackoverflow.com/questions/38306016/populating-a-table-with-all-dates-in-a-given-range-in-g...​

MySQL

select * from

(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from

(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,

(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,

(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,

(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,

(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v

where selected_date between '2012-02-10' and '2012-02-15'

Source: https://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-sele...​

In Looker using BigQuery:

Step 1: Create derived_table with date generation logic

​​

Step 2: Create an explore that uses the date_table as it's base, joining in subsequent view files on the generated date field

​​

End Result:

​