Calculate Number of Quarters from a Date

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


  }