Calculate Number of Quarters from a Date

bigquery

(John Gallinagh) #1

I have a requirement to calculate the number of weeks from a date dimension. For example date value is 2018-05-20 which is in Q2 (for us), I need to figure out if there are 13 or 14 weeks in this quarter. How can this be accomplished?


(jeffrey.martinez) #2

John, good question! We have a pattern documented in this thread that calculates the days in a quarter. There is also an option to extract the index of weeks in that quarter.

Your use case may be a bit more involved, in particular if you want to account for 92 day quarters (that would include a 14th week index), then instead of the case statement for the week of quarter index dimension:

dimension: weekofquarterindex { 
    type: number             #generates week of quarter index int 1-14
    sql: CEILING(${dayofquarterindex}/7.0)  ;;
  }

Instead of this (from the pattern)

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

(John Gallinagh) #3

Thank you!