Let’s say we have records of user milestones:
user_id | event_a | event_b | event_c | …
1 | time_a | time_b | time_c | …
We’d like users to be able to calculate differences between two arbitrary event times on the fly, as well as measures on them. For example, answering the question “What is the average time elapsed between event_x and event_y for a user?” for arbitrary x and y.
Short of hard-coding dimensions for all n^2 pairs of events, is there a way to enable this?
Hi Clara,
Great question! You can definitely accomplish the on-the-fly part of this with table calculations. You’d just need to pull in the milestone time fields and do a date diff between them. You could also use another table calc to do additional aggregations like avg on that list of time diffs, however the avg calculation would show up in every row of your result set.
At the moment we don’t have a great way to do this in lookml, but if you don’t have too many milestones you could write dimensions for date diffs between each one and if you had a need to explore time between event_1 and event_6 for example you could write another dimension that adds 1-6. We’re happy to discuss either approach more here or on chat!
Well, there is a way you can solve this. It is undocumented because we really don’t like the user interface it presents so we are going to make a new and better way for this to work in the future. There is enough code lying around using this construct that we’ll continue to support it.
explore: events {}
view: events {
dimension: event_a_time {type: date_time}
dimension: event_b_time {type: date_time}
dimension: event_c_time {type: date_time}
filter: start_event_time_name {
suggestions: [event_a, event_b, event_c]
}
filter: end_event_time_name {
suggestions: [event_a, event_b, event_c]
}
dimension: start_time_dynamic {
type: date_time
sql:
CASE
WHEN {% parameter start_event_time_name %} = 'event_a'
THEN ${event_a_time}
WHEN {% parameter start_event_time_name %} = 'event_b'
THEN ${event_b_time}
WHEN {% parameter start_event_time_name %} = 'event_c'
THEN ${event_c_time}
end;;
}
dimension: end_time_dynamic {
type: date_time
sql:
CASE
WHEN {% parameter end_event_time_name %} = 'event_a'
THEN ${event_a_time}
WHEN {% parameter end_event_time_name %} = 'event_b'
THEN ${event_b_time}
WHEN {% parameter end_event_time_name %} = 'event_c'
THEN ${event_c_time}
end;;
}
dimension: dynamic_time_difference {
type: number
sql: DATE_DIFF(${start_time_dynamic}, ${end_time_dynamic}) ;;
}
measure: average_dynamic_time_difference {
type: average
sql: ${dynamic_time_difference} ;;
}
}
Will produce an explore that works like:
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.