Sum of Time


(Paul Wadsworth) #1

I’ve got a View, which unfortunately has some fields in a Time format.
I was trying to add a Measure so people can get the SUM Time for their reports, but I can’t work out how to do it.

Previously I’d keep everything as an INT of seconds, and then change it to time right at the end in the reports, but this time I’m stuck with the data in a Time format.

Thanks for any help


(Daan Visker) #2

Hi Paul,

What might be able to help you is using the ‘extract_seconds({datetime_raw})’ function in a (custom) dimension.

If you want to use this in just one view, you can use the Custom Fields -> New -> Custom Dimension, available at the top of all available Views in your Explore.
Here you can manually create dimensions just for that view.

Create the custom dimension: extract_seconds(${view.datetime_raw})

After that, you can create a custom measure to Sum on that Custom Field. Or take the difference between two raw time-fields first.

I hope this helps you forward!

Kind regards,

Daan


(bernard.kavanagh) #3

Hi Paul,

Thanks for reaching out. As Daan says above, creating a dimensions using a custom function like EXTRACT() or taking the difference between two raw time-fields using a DATEDIFF() to extract seconds will both work.

However in Looker 6 we just announced a new LookML dimension/dimension_group of type: duration

This essentially overrides the need for a DATEDIFF()dimension.

Within this dimension group you can specify the intervals you require:

dimension_group: duration {
    type:  duration
    intervals: [second, minute, hour, day, week, month, quarter, year]
    sql_start: ${orders.created_raw} ;;
    sql_end: ${returned_raw} ;;
  }

And as Daan says above, you can then create a custom measure based on these timeframes:

Hope this helps!!!

Bernard