Question

How to Set Multiple Week Definitions in Looker


Each company handles reporting a little differently. Sometimes individual departments like finance might have strict reporting criteria for tax purposes, while a marketing team might operate on a different reporting schedule. More often than not, this requires several manual hours of cleaning the data before any analysis or presentation can be done with it. This is a huge time suck that should be easier to handle.


Tools like Looker have built features in place to actually handle this issue. Looker has the ability to set a parameter for your whole data model on which day it considers to be the start of the week. You can use the parameter “week_start_day:” and this will set it universally for the whole data model. By default Looker sets the start of the week to Monday. Using this feature however, can alleviate many hours spent on adjusting reporting manually in Excel or Google Sheets. But you could run into the issue of not wanting to set the week start day for ALL your reporting and only a subset of it. Looker doesn’t have that functionality built in so we have to get a little more creative on how we handle that. Luckily there is a solution for it.


If you want to set the week’s start day to something other than the default and only want it set for certain Views you can do that pretty easily by using a design pattern below:


Redshift:

This sets the week start day to Saturday and end of Friday.


dimension: shifted_week_reporting {
type: date
Convert_tz: no
sql: CASE
WHEN ${datefield_day_of_week} = 'Saturday' THEN ${datefield_date}
WHEN ${datefield_day_of_week} = 'Sunday' THEN dateadd(days, -1, ${datefield_date})
WHEN ${datefield_day_of_week} = 'Monday' THEN dateadd(days, -2, ${datefield_date})
WHEN ${datefield_day_of_week} = 'Tuesday' THEN dateadd(days, -3, ${datefield_date})
WHEN ${datefield_day_of_week} = 'Wednesday' THEN dateadd(days, -4, ${datefield_date})
WHEN ${datefield_day_of_week} = 'Thursday' THEN dateadd(days, -5, ${datefield_date})
WHEN ${datefield_day_of_week} = 'Friday' THEN dateadd(days, -6, ${datefield_date})
END;;
}

Snowflake:

This sets the week start day to Wednesday and end of Tuesday

.


dimension: shifted_week {
type: date
Convert_tz: no
sql: CASE
WHEN ${datefield_day_of_week} = "Wednesday" THEN ${datefield_date}
WHEN ${datefield_day_of_week} = "Thursday" THEN date_add(days, -1, ${datefield_date})
WHEN ${datefield_day_of_week} = "Friday" THEN date_add(days, -2, ${datefield_date})
WHEN ${datefield_day_of_week} = "Saturday" THEN date_add(days, -3, ${datefield_date})
WHEN ${datefield_day_of_week} = "Sunday" THEN date_add(days, -4, ${datefield_date})
WHEN ${datefield_day_of_week} = "Monday" THEN date_add(days, -5, ${datefield_date})
WHEN ${datefield_day_of_week} = "Tuesday" THEN date_add(days, -6, ${datefield_date})
END;;
}

Implementation:


If you would like to implement this code you must ensure you have a time dimension_group already defined. In that dimension_group you will also need to specify a timeframe for “day_of_week” and “date”. Once you have those defined you can replace the “ ${datefield_day_of_week}” and “${datefield_date}” in the pattern above.


For example, suppose you have the following date dimension defined for a customer account creation date:


dimension_group: account_create {
description: "Date a customer account was created"
type: time
timeframes: [
raw,
time,
date,
day_of_week
week,
month,
quarter,
year
]
sql: ${TABLE}.account_create_date ;;
}

Then suppose you want to report on customer accounts created with weeks starting on Saturday. You would simply specify the custom week definition as follows:


dimension: shifted_week {
type: date
Convert_tz: no
sql: CASE
WHEN ${account_create_day_of_week} = "Saturday" THEN ${account_create_date}
WHEN ${account_create_day_of_week} = "Sunday" THEN date_add(days, -1, ${account_create_date})
WHEN ${account_create_day_of_week} = "Monday" THEN date_add(days, -2, ${account_create_date})
WHEN ${account_create_day_of_week} = "Tuesday" THEN date_add(days, -3, ${account_create_date})
WHEN ${account_create_day_of_week} = "Wednesday" THEN date_add(days, -4, ${account_create_date})
WHEN ${account_create_day_of_week} = "Thursday" THEN date_add(days, -5, ${account_create_date})
WHEN ${account_create_day_of_week} = "Friday" THEN date_add(days, -6, ${account_create_date})
END;;
}

The last step is to ensure Looker understands the way you want to filter on this new dimension. Add your date filter on the dimension you have derived the shifted_week dimension off of. Next select Matches(Advanced) and have it set to “saturday-14 days for 14 days”. This tells Looker our week starts on Saturday and we want data from the last 14 days for all 14 days. You can easily change this to week ago by changing the code to “saturday-7 days for 7 days”.


11 replies

Hi there! Inspired by the solution from Diego I created the Hadoop Impala version of the solution:

 

parameter: weekstart {
label: "Week Start"
group_label: "Pickers & Parameters"
type: unquoted
hidden: no
allowed_value: {
label: "Monday"
value: "0"
}
allowed_value: {
label: "Tuesday"
value: "1"
}
allowed_value: {
label: "Wednesday"
value: "2"
}
allowed_value: {
label: "Thursday"
value: "3"
}
allowed_value: {
label: "Friday"
value: "4"
}
allowed_value: {
label: "Saturday"
value: "5"
}
allowed_value: {
label: "Sunday"
value: "6"
}
default_value: "0"
}

dimension: week_shifted{
datatype: date
type: date
sql: date_add(date_trunc('WEEK', date_add(${time_stamp_date}, -{% parameter weekstart %})), {% parameter weekstart %}) ;;
}

 

Hi! I think there is better way that also makes this dynamic. I use a liquid variable to pass the starting day of the week (selected through a parameter) to the date_trunc function:

 

##Bigquery Implementation

parameter: weekstart {
type: unquoted
allowed_value: {
value: "MONDAY"
}
allowed_value: {
value: "TUESDAY"
}
allowed_value: {
value: "WEDNESDAY"
}
allowed_value: {
value: "THURSDAY"
}
allowed_value: {
value: "FRIDAY"
}
allowed_value: {
value: "SATURDAY"
}
allowed_value: {
value: "SUNDAY"
}

}

dimension: week_shifted_by_param{
datatype: date
type: date
sql: date_trunc(${date}, week ({% parameter weekstart %})) ;;


}

 

@afzaljasani I implemented the code snippet in one of my view. But there is issue that the query generates and display null values as well. But if I run the same query in SQL Runner and SQL Server there are no null values.

Can you please help why this would be happening.

Hi @florianhendrickx happy to hear you are using this now! I originally had created that code with Redshift in mind. I have found that in BigQuery you generally need to use looker’s datatype parameter to get things to work properly.

Hi,


I’m running on Bigquery and mapped this code.

See below if you want to use it.

It works, but if I try to filter as suggested with the ‘matches advanced: “saturday-14 days for 14 days”’ I get this error when running: No matching signature for operator >= for argument types: DATE, TIMESTAMP. Supported signature: ANY >= ANY at [34:11]


I think my the generated code is wrong.

Any idea how to solve this or is it a looker bug.


  dimension: createdate_sprintweek {
type: date
sql: CASE
WHEN ${createdate_day_of_week} = 'Sunday' THEN DATE_SUB(${createdate_date}, INTERVAL 1 DAY)
WHEN ${createdate_day_of_week} = 'Monday' THEN DATE_SUB(${createdate_date}, INTERVAL 2 DAY)
WHEN ${createdate_day_of_week} = 'Tuesday' THEN DATE_SUB(${createdate_date}, INTERVAL 3 DAY)
WHEN ${createdate_day_of_week} = 'Wednesday'THEN DATE_SUB(${createdate_date}, INTERVAL 4 DAY)
WHEN ${createdate_day_of_week} = 'Thursday'THEN DATE_SUB(${createdate_date}, INTERVAL 5 DAY)
WHEN ${createdate_day_of_week} = 'Friday' THEN DATE_SUB(${createdate_date}, INTERVAL 6 DAY)
WHEN ${createdate_day_of_week} = 'Saturday' THEN ${createdate_date}
END;;
datatype:date
}

Solved it thanks to support, code needed a datatype:date

Edited the snippet

Thank you.

Using this solution would allow you to specifically filter the week start day for the view when you need to. Since you will be creating more dimensions off of the current date dimensions you can use the new dimensions for the looks/dashboards where you need the start date to be changed. This will not affect any other views since the new dimensions are only created on a specific view.


This will not allow you to filter the start day of your choice for any given look/dashboard unless you edit the view related to those looks/dashboards.

Yes.


I am wanting to change week start day for a specific look/dashboard composed of measures and dimensions in one view. This one view also feeds other looks/dashboards that I don’t want to change the week start day on.


Does this work around only allow you to change the week start day for the entire view or does it allow you to filter on the start day of your choice for any given look/dashboard.


I understand this is a step down from changing an entire model week start day.

Hi @clucas444 could you elaborate a little more?

Am I correct in thinking this work around still changes an entire view and not just a look/dashboard?

Userlevel 7
Badge +1

Added some code blocks to prettify this already very lovely post 🙂


Thanks for sharing, Afzal.

Reply