Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

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

  • 8 January 2015
  • 8 replies
  • 365 views
L
Rank
Userlevel 5
Badge


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



8 replies

nbeyer
Rank
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.

lloydtabb
Rank
Userlevel 6
Badge

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
Joshua_Moskovitz
Rank
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

    sql_case: 

      1: ${dayofweekname} = 'Saturday'

      2: ${dayofweekname} = 'Sunday'

      3: ${dayofweekname} = 'Monday'

      4: ${dayofweekname} = 'Tuesday'

      5: ${dayofweekname} = 'Wednesday'

      6: ${dayofweekname} = 'Thursday'

      7: ${dayofweekname} = 'Friday'
L
Rank
Userlevel 5
Badge

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

Joshua_Moskovitz
Rank
Userlevel 3

This is true!

A

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 ?

A

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:


  TO_CHAR((

    (CASE

      WHEN EXTRACT(DOW FROM sent_at) < 2

        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.

L
Rank
Userlevel 5
Badge

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

Reply


Terms of Service