As of Looker 6.2, developers can use the type: duration
parameter with a dimension group instead of the method described in this article.
If you do not have developer access, check out the article on Computing the Difference between Dates (for non-developers) for a method that uses table calculations to achieve a similar effect.
The Problem
I have two dates and I'd like to know how much time has elapsed between them. I might want to know this result in days, months, or years.
The Solution
This can be written into LookML as a dimension of type: number
. The LookML will look something like this:
Note: The SQL in this example may need to be updated in accordance with your database SQL dialect.
dimension: date_diff { type: number sql: DATEDIFF( day, ${created_raw}, ${deleted_raw}) ;; }
Notice the use of the raw
timeframe. This timeframe returns the timestamp from the database, as opposed to other timeframes, which are cast to strings using SQL to format the date.
Grouping together Multiple Dimensions
For example, we take this approach for several different time interval types, such as day
, month
, and year
. We can group these together in the field picker using the group_label
parameter, like so:
dimension: date_diff_day { type: number label: "By Day" group_label: "Difference" sql: DATEDIFF( day, ${created_raw}, ${deleted_raw}) ;; } dimension: date_diff_month { type: number label: "By Month" group_label: "Difference" sql: DATEDIFF( month, ${created_raw}, ${deleted_raw}) ;; } dimension: date_diff_year { type: number label: "By Year" group_label: "Difference" sql: DATEDIFF( year, ${created_raw}, ${deleted_raw}) ;; }
The end result looks like this: