How to Create a Quarter Timeframe Dimension (prior to 3.34)

deprecated
#1

As of Looker 3.34, we have introduced a built-in quarter timeframe. Read more about using it here.

Prior to Looker 3.34, there was no built-in “quarter” timeframe. However, you can manually create a quarter timeframe dimension as follows.

Consider a dimension_group like this one:

- dimension_group: created
  type: time 
  timeframes: [time, date, month, year] 
  sql: ${TABLE}.created_at

To create a quarter timeframe, use the EXTRACT function in a new dimension, like this:

####MySQL

 - dimension: create_quarter
    sql: | 
      CONCAT( 
         CONCAT(
            cast(EXTRACT(year FROM ${TABLE}.created_at) as char),
             ' - Q' 
             )
         ,
         cast(EXTRACT(quarter FROM ${TABLE}.created_at) as char)
     )

####Postgres and Redshift

- dimension: created_quarter 
  sql: | 
    cast(EXTRACT(year FROM ${TABLE}.created_at) as varchar) 
    || 
    ' - Q' 
    || 
    cast(EXTRACT(quarter FROM ${TABLE}.created_at) as varchar)

####Microsoft SQL Server

- dimension: created_quarter 
  sql: | 
    cast(DATEPART(year, ${TABLE}.created_at) as varchar) 
    || 
    ' - Q' 
    || 
    cast(DATEPART(quarter, ${TABLE}.created_at) as varchar)

In the Explore UI, this dimension will appear alongside the other dimensions, not in your dimension_group.

1 Like

(Gruen) #3

If your Looker instance does timezone converting, this solution won’t fly unless you also recast the timezones OR use the Looker-derived dimensions:

  - dimension: created_quarter 
     sql: | 
       cast(EXTRACT(year FROM ${created_time}) as varchar) 
       || 
       ' - Q' 
       || 
       cast(EXTRACT(quarter FROM ${created_time}) as varchar)

Redshift/Postgres example above.

2 Likes

#4

Great point! Using Looker dimension groups in this case is probably the best way to do this, especially if you have timezone conversion. Note that this may cause issues, since Looker time fields are usually cast to strings. To get around this, you’ll want to re-cast the values to date/datetime/timestamp/etc. Read more about that here.

0 Likes

(Vineela1208) #5

this can also be used if the fiscal starts from Jan

- dimension: quarter_from_Jan
  sql: |
    CONCAT(${creation_year},'Q/',CEILING(${creation_month}/3))
1 Like

(Adam Rhuberg) #6

After creating a custom quarter dimension, how would you structure a filter within a Look to make sure that a report only shows data through the most recent completed quarter? I’d like to avoid hard-coding date filters if possible and instead would like to show only a rolling 8 quarter view. Any thoughts on how to do that dynamically within a Look would be much appreciated.

0 Likes

#7

The best way to do this currently would be to create a yesno dimension that returns Yes when a date is within the last 8 quarters. You could create this by using your SQL dialect’s underlying quarter functions. Then you could filter on this dimension in Explore.

That being said, we are working on a quarter timeframe and filtering that should be available in a coming release!

0 Likes

(Margaret Rosas) #8

The quarter timeframe is now shipping in Looker 3.34. Read more about how to implement quarters here.

0 Likes