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

[sam8]

For putting this into a dimension, you’d do:

dimension: biweekly {
  type: string
  group_label: "created"
  sql: 
CASE WHEN 
MOD(extract(week from ${created_raw}),2)
THEN TO_CHAR(DATE_TRUNC('week', DATEADD(week,1,${created_raw})), 'YYYY-MM-DD')
ELSE TO_CHAR(DATE_TRUNC('week', ${created_raw}), 'YYYY-MM-DD') END;;
}

This assumes you have a created dimension group defined like so:

dimension_group: created {
  type: time
  group_label: "created"
  timeframes: [time, date, raw]
  sql: ${TABLE}.createddate
}

Note the use of group_label to put your new dimension into the original dimension group.