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”.
