I need a Bi-Weekly Timeframe

[William_Lane]

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:

SELECT 
--TO_CHAR(DATE_TRUNC('week', CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', account.createddate)), 'YYYY-MM-DD') AS "account.createddate_week", --Looker-generated week SQL

CASE WHEN 
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 
LIMIT 500