Quarter-To-Date Trending by Week Index Chart

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?

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.

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 ;;


   }

.

Doh… Thank you Caitlin, this is great!!