Quarter-To-Date Trending by Week Index Chart

dashboards
lookml

(Wyatt) #1

I like to track Quarter vs Last type trends. EG in the following chart:

The challenge I have in accomplishing this in Looker is that I have a date field, but I don’t have a “dayOfQuarterIndex” or “weekOfQuarterIndex” field.

The data table I would need to create would look like this:

Does anyone know how to create the quarter’s week index from a “Created Date” field? (The created date field is in standard iso format YYYY-MM-DD with a timezone offset).

Thanks,
Wyatt


(jeffrey.martinez) #2

Hey Isaac, (or @wyatt for tagging)

In Looker we are able to do year over year, month over month, week over week, comparisons but no easy way to do dayofquarterIndex or weekofquarterIndex. I can show our product team this use case, though. For the time being, this most likely will take some creative SQL. What SQL dialect are you using?


(Wyatt) #3

Thank you for the response!

Using Redshift.

I saw an article about how to do this using excel formulas using the modulo operation, so this might be achievable with SQL or lookml.

Thanks for any help you can offer!


QoQ Line chart comparisons, stop line at current date
(jeffrey.martinez) #4

Thanks @wyatt

Give this a try in LookML, constructed using a created_date time dimension_group. We define 3 new dimensions. We make a new dimension “q1daycount” to make sure we are considering leap years, then we use a case statement to build “dayOfQuarterIndex” and “weekOfQuarterIndex”:

  dimension_group: created_date {
    type: time
    timeframes: [  #for our purposes we need doy, qoy, year
      raw,
      date,
      day_of_year,     #1-366 integer
      ...
      quarter_of_year, #Q1-Q2-Q3-Q4 string for dayofquarterindex case statement
      year             #integer, used to account for leap years in q1daycount
    ]
    datatype: date
    sql: ${TABLE}.created_date ;; 
  }

  dimension: q1daycount { #accounts for 91 days in Q1 during leap years
    type: number
    sql: CASE
    WHEN MOD(${created_date_year}, 4) = 0 THEN 91 
      ELSE 90 END;;
  }

  dimension: dayofquarterindex { #returns day of quarter index int 1-92
    type: number
    sql: CASE 
    WHEN ${created_date_quarter_of_year} = 'Q1' THEN ${created_date_day_of_year}
    WHEN ${created_date_quarter_of_year} = 'Q2' THEN (${created_date_day_of_year}-${q1daycount})
    WHEN ${created_date_quarter_of_year} = 'Q3' THEN (${created_date_day_of_year}-(${q1daycount}+91))
    WHEN ${created_date_quarter_of_year} = 'Q4' THEN (${created_date_day_of_year}-(${q1daycount}+183))
   ELSE 0 END ;;
   }

  dimension: weekofquarterindex { #defaults to 13 when dayofquarterindex is 92
    type: number             #generates week of quarter index int 1-13
    sql: CASE 
    WHEN ${dayofquarterindex} = 92 THEN 13
    ELSE CEILING(${dayofquarterindex}/7.0) END  ;;
  }

Here’s an example output with the original date field, the dayofquarterindex, then the weekofquarterindex:

Depending on when your quarters/fiscal years start and stop, this may need to be adjusted slightly. Also, in this example we counted the 92nd day of a quarter as still in week 13, despite technically being in week 14. Hope this helps.


Calculate Number of Quarters from a Date
(Wyatt) #5

I’ll give this a shot! Thank you for a thorough review and explanation.

When I get it working I’ll update this thread.


(Caitlin Moorman) #6

A simpler alternative way to calculate the dayofquarterindex is the following:

dimension: dayofquarterindex {   #returns day of quarter index int 1-92
    type: number
    sql: DATEDIFF('day',date_trunc('quarter',${created_raw}),${created_date}) + 1 ;;
   }

.


(Izzy) #7

Super elegant, nice :grinning:


(jeffrey.martinez) #8

Doh… Thank you Caitlin, this is great!!