How would I use the new duration type in a dimension group?


(Jordan Millar) #1

I was wondering what the right way to use the new duration type is in our use case.

We collect the responses to questions and part of that is the number of seconds that the user took to answer the question in a field we call duration_answered.

Would this field be a candidate for the new duration type? Do we use duration to be able to quickly allow the user to show it as minutes or hours? How do I display combinations like mm:ss or hh:mm:ss for this field?

Appreciate any assistance as we are very new to Looker!


(milli.koch) #2

Hi @jordan.millar!

This would depend on how you’re measuring duration_answered. If your data includes raw timestamps for response start and end times then this would be a perfect candidate for the new duration type as you could use these fields with the sql_start and sql_end parameters. That would look something like this:

dimension_group: response_duration {
  type: duration
  intervals: [second, minute, hour]
  sql_start: ${started_raw} ;;
  sql_end: ${ended_raw};;
}

In the case that this field is simply measured as a number of seconds without start and end times then we would need to define the intervals in a slightly different way. One way to do this would be to use the sql parameter to create the different timeframes and then to use group_label to group these timeframes in the field picker.

Something like:

dimension: seconds {
    group_label: "Response Duration"
    type: number
    sql: TABLE.${duration_answered} ;;
  }
  
  dimension: minutes {
    group_label: "Response Duration"
    type: number
    sql: ${seconds}/60 ;;
  }
  
  dimension: hours {
    group_label: "Response Duration"
    type: number
    sql: ${seconds}/3600 ;;
  }

As for time formatting, this can be achieved with the value_format parameter. Just note that for the hh:mm:ss format the value needs to be in number of days, so for seconds we would need to divide by 86400 first.

dimension: seconds {
    group_label: "Response Duration"
    type: number
    sql: TABLE.${duration_answered}/ 86400.0 ;;
    value_format: "hh:mm:ss"
  }

Also note that the value_format parameter can currently only be used in measures and dimensions – not dimensions groups (including of type duration) – but I will make sure to provide your feedback on this to the product team!

Best,
Milli