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...
This content is subject to limited support.