Question

Calculate Number of Quarters from a Date

  • 23 May 2018
  • 2 replies
  • 898 views

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!

Reply