Question

Quarter-To-Date Trending by Week Index Chart

  • 6 November 2017
  • 7 replies
  • 1613 views

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


7 replies

Userlevel 3

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?

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!

Userlevel 3

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.

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



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

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

}



.

Userlevel 7
Badge +1

Super elegant, nice 😀

Userlevel 3

Doh… Thank you Caitlin, this is great!!

Reply