Timeframes and Dimension Groups in Looker

  • 15 January 2015
  • 5 replies
  • 10032 views

Userlevel 5
Badge



Read about dimension groups in our docs here as well as available timeframes here.







When analyzing time-based data, it’s not uncommon to want to be able to think in terms of several different time frames. Say you’re looking at a field like order_created_at and want to be able to think in terms of date, week, and month. You could create three dimensions: one for each time frame. This is something of a pain, though, and can open up room for error (typographical or otherwise).




Introducing: The dimension_group




Fortunately, we created the dimension_group to simplify the process of defining logically associated dimensions.




  - dimension_group: order_created_at


type: time


timeframes: [date, week, month]


sql: ${TABLE}.order_created_at





This code is equivalent to writing out each of the dimensions individually, but has a few advantages.







  • It’s less prone to error



  • It’s more flexible



  • It’s easier to write



  • It’s easier to read



  • It’s already organized






You could always write things out if you really wanted to. Let’s run through that process.




The Long Way




Three Dimensions




Writing out a few time-based dimensions is easy enough. Just create dimensions for each timeframe and you’re set. Maybe you’ll most commonly analyze by date, so it’ll be the default. It makes referencing the order_created_at date time frame a little easier. This would work, and look something like:




  - dimension: order_created_at


type: date


sql: ${TABLE}.order_created_at





- dimension: order_created_at_month


type: date_month


sql: ${TABLE}.order_created_at





- dimension: order_created_at_week


type: date_week


sql: ${TABLE}.order_created_at





Things Get Messy




This is all fine and dandy, but what happens if you want to start thinking about the time an order was placed? In order to pull that field up in an Explore or visualization, you’d have to create a new dimension:




  - dimension: order_created_at_time


type: date_time


sql: ${TABLE}.order_created_at





As you flesh out your analyses, you’d have to keep on defining new dimensions, which can be a pain. It’s a lot to read through, and might even mess with your code organization. Even worse, what if you accidentally pick the wrong type?




Things get messy and tedious quickly. Plus, as your group of time dimensions grows, the margin for error does too. No good.




Back to the Group




Adding to your dimension group is as simple as attaching another [timeframe] (http://www.looker.com/docs/reference/lookml-reference/dimension-lookml-reference#timeframes) to the list.




 - dimension_group: order_created_at


type: time


timeframes: [date, week, month, time]


sql: ${TABLE}.order_created_at





This (much simpler) block of LookML is nearly equivalent to the two blocks outlined above. It’s easier to write, the code organization is taken care of, and, as you can see, it’s much cleaner. Everyone’s better off, right? Almost.




Calling upon Your Dimension Group




Referencing these dimensions takes some care. You won’t be able to reference your friend order_created_at. It doesn’t exist the same way as when we declared it like dimension: order_created_at (it’s now order_created_at_date). In order to call the rest of the gang, you’ll have to append the appropriate timeframe.




In order to reference the members of the dimension group in your LookML, you’ll need to ask for them like this:




${order_created_at_time}


${order_created_at_date}


${order_created_at_week}


${order_created_at_month}





Keep this in mind and it’ll help you avoid messages like [warning field not found] (https://discourse.looker.com/t/warning-field-not-found/171) and [unknown field substitution] (https://discourse.looker.com/t/error-unknown-field-substitution/151).


5 replies

Userlevel 2

Readers should note that this UI is now possible not only for type: time dimensions: see Grouping Fields Within Views (3.44+).

Userlevel 7
Badge +1

3 posts were split to a new topic: https://github.com/looker/analytics/issues/8247#

dimension_group works fine. However, is it possible to sort de date types in your explore f.e. to day, week, month? Looker is default sorting this to day, month, week.

Are we able to provide a custom format for each timeframe in group_dimensions? Currently, charts using the timeframes displayed like 2021-07-07 for “day”, but can I display it like “Jul 7, 2021” without touching the custom format in explore? 

Are we able to provide a custom format for each timeframe in group_dimensions? Currently, charts using the timeframes displayed like 2021-07-07 for “day”, but can I display it like “Jul 7, 2021” without touching the custom format in explore? 

You can do this using the link below. Basically you will have to define all the formats within the groups yourself. 

 

https://help.looker.com/hc/en-us/articles/360023800253-Easy-Date-Formatting-with-Liquid

Reply