Bi-Weekly timeframes can be really useful! For now, using SQL is the best way to achieve this.
In Redshift, here’s one way to go about it. Read the comments through the SQL to understand what each line does:
--TO_CHAR(DATE_TRUNC('week', CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', account.createddate)), 'YYYY-MM-DD') AS "account.createddate_week", --Looker-generated week SQL
MOD(extract(week from account.createddate),2) -- this returns 0 or 1 based on whether the week number (0-51) is odd or even
THEN TO_CHAR(DATE_TRUNC('week', DATEADD(week,1,account.createddate)), 'YYYY-MM-DD') --if it's an even numbered week, add a week to it
ELSE TO_CHAR(DATE_TRUNC('week', account.createddate), 'YYYY-MM-DD') END -- if it's an odd numbered week, leave it alone.
AS "order_items.returned_biweek" --this way you get groups of two weeks, rather than just one.
--now if you group by this biweekly dimension, it will account for both weeks.
FROM public.account AS account
GROUP BY 1
ORDER BY 1 DESC
For putting this into a dimension, you’d do:
This assumes you have a
createddimension group defined like so:
Note the use of
group_labelto put your new dimension into the original dimension group.