Knowledge Drop
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...
LinkedIn
Twitter