Calculate Number of Quarters from a Date

  • 23 May 2018
  • 2 replies

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?

2 replies

Userlevel 3

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


Thank you!