[RETIRED] Creating Week Dimensions that Don't Start on Monday (prior to 3.38)

  • 8 January 2015
  • 8 replies

Userlevel 5

This article has been retired. The information is now in the Looker documentation on this page.

8 replies

Userlevel 3

Note that even if you want your week to start on a day besides Sunday you still need to keep the '-6 days' in the final part of the statement to properly offset.

Userlevel 6

On of the problems with this is that you might think you set an explicit date filter each time you run this Look, you don’t. You can use the custome relative day of week filter syntax. The syntax is of the form:

   <day of week> - <interval> for <interval>

The day of week is date of the most recent day of the week in the past or today.

If you want to make a report that shows the last complete week you can set a custom filter

Created Date: sunday-14 days for 7 days

Or the current week.

Created Date: sunday-7 days for 7 days
Userlevel 3

Has anyone tried to do this using a sql_case field that sets the index for each day of week, then an order_by_field based on that field? You can then hide the index field:

  - dimension: dayofweekname
label: 'Day of Week Name'
sql: ${TABLE}.dayofweekname
order_by_field: dayofweekindex

- dimension: dayofweekindex
hidden: true
1: ${dayofweekname} = 'Saturday'
2: ${dayofweekname} = 'Sunday'
3: ${dayofweekname} = 'Monday'
4: ${dayofweekname} = 'Tuesday'
5: ${dayofweekname} = 'Wednesday'
6: ${dayofweekname} = 'Thursday'
7: ${dayofweekname} = 'Friday'
Userlevel 5

This is a great other way to do this if you are looking at day of week data! But in order to look at data grouped by week, you probably still need to create a week starting Sunday dimension

Userlevel 3

This is true!

Reading WHEN EXTRACT(DOW FROM ${created_date}) <= 6, I’m wondering if this can ever be false, since EXTRACT(DOW) can only yield 0 to 6 ? At least according to http://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html

Something I missed here ?

Oh I guess it’s for the case when you want to start in the middle of the week, right ? For you to tweak to something other than ‘6’ !

So from my findings, if you want to have a week that starts on a Tuesday, you need to change the 6 (but not the last -6 days) for the value of the LAST day of the week you want. So you would change the 6 to 1 (Monday) to have weeks that start on Tuesday.

Or even better:

THEN sent_at + (2 - EXTRACT(DOW FROM sent_at)) * INTERVAL '1 days'
ELSE sent_at + ((2 + 7) - EXTRACT(DOW FROM sent_at)) * INTERVAL '1 days'
END) + INTERVAL '-7 days'),
'YYYY-MM-DD') AS week_start

which is easier to reason about (-6 days ?!). In here I took out the <= and changed it for <, and changed -6 days to -7 days. And now you put the day you want the week to start off of. This one still starts its day off Tuesday.

Userlevel 5

As of Looker 3.38, we’ve added a way to set week start day across an entire model! Check it out here.