Hello,
I have a single table with multiple datetime columns that represent when a row in my table transitioned through various events. A simplified example of this table would look like:
id | created_at | registered_at | downloaded_at
12345 | 2020-01-01 | 2020-01-02 | 2020-01-03
67890 | 2020-01-02 | 2020-01-03 | 2020-01-04
In practice each of those dates is a full timestamp, but I would truncate them down to dates for purposes of this visualization. I want to build a table visualization that shows counts of each event per day like so:
Date | # Created | # Registered | # Downloaded
2020-01-01 | 1 | 0 | 0
2020-01-02 | 1 | 1 | 0
2020-01-03 | 0 | 1 | 1
2020-01-04 | 0 | 0 | 1
I’m unsure what the best way to go about modeling this data in Looker would be. I imagine I need something like a derived table that generates every date and then use this to join on the three date columns in my event table? I’m fairly new to Looker and not sure exactly what that might look like.
Thanks in advance for any recommendations!