Day of week in custom dimension?

(Ravi Mody) #1

Hi, is it possible to extract day of week or day of week index in custom dimensions or table calculations? I don’t see anything in https://docs.looker.com/exploring-data/creating-looker-expressions/looker-functions-and-operators?version=5.22. Thanks!

0 Likes

(Ryan Dunlavy) #2

Hi @ravimody,

We can get the day of week index in a table calculation by using a combination of the `diff_days` and `mod` functions:

``````mod(diff_days(date(2008,01,01), \${orders.created_date}) + 1, 7)
``````

This will return the day of week index with Monday being 0.

If using Redshift or Postgres, this formula will need to be modified to include the round function:

``````mod(round(diff_days(date(2008,01,01), \${orders.created_date}) + 1), 7)
``````

We can also get the day of week using this same formula and writing in the days for each number like this:

``````index(
list("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"),
mod(diff_days(date(2008,01,01), \${orders.created_date}) + 1, 7)
)
``````

Hope this helps!

Ryan

0 Likes

(Ravi Mody) #3

This is great Ryan, thanks!

0 Likes

(Nick Morrison) #4

You can also specify a timeframe in your table View to add “day of week” as a field or filter provided that your database is referencing a timestamp.

Example:

``````  dimension_group: created {
label: "Page Creation"
type: time
timeframes: [
raw,
hour_of_day,
time,
date,
day_of_week,
week_of_year,
month,
month_name,
quarter,
year,
day_of_month
]
sql: \${TABLE}.created_at ;;
}
``````
0 Likes

(Alex Sherwood) #5

This works great for me except I’m getting null values instead of Sundays. I expect that’s because the first day of the week is set to Sunday in our instance of Looker. Is there a way to adapt this formula to allow for that?

0 Likes