month_x timeframe (Community)

View the original community article here

Last tested: Feb 3, 2019
 

This came from a custom month_18 (group by every 1.5 years) timeframe in redshift. Here is a generalized version for month_x where you can shift the start month by adjusting y:

  dimension: month_x {    type: string    sql: TO_CHAR(DATE_TRUNC('month',                #Convert the result to an appropriate string DATEADD('month',                                                  # Going to add months at the end     (DATE_PART(month,CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', ${created_raw} ))::integer +      #These two lines calculate the number of months from 0000-01-01 to created_raw      DATE_PART(year, CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', ${created_raw} ))::integer*12) -             (DATE_PART(month,CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', ${created_raw} ))::integer -1+y+ #these two lines use Modulo X to subtract off the appropriate number of months to group by x (and shift by y)      DATE_PART(year, CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', ${created_raw} ))::integer*12) % x        -1,       '0000-01-01')        #This is the end of the dateadd       ), 'YYYY-MM')  ;;    group_label: "Created Date"    }

Examples:
for x=18, y=0; you get 2013-01, 2014-07, 2016-01, 2017-07
for x=18, y=6; you get 2012-07, 2014-01, 2015-07, 2017-01
for x=4, y=0; you get 2013-01, 2013-05, 2013-09, 2014-01, 2014-05, 2014-09, 2015-01, 2015-05, 2015-09...

​