Question

Day of week in custom dimension?

  • 17 September 2018
  • 8 replies
  • 4727 views

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!


8 replies

Userlevel 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

This is great Ryan, thanks!

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 ;;
}

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?

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

I found a different way to do this. I wanted to highlight Weekends in our visualizations. I added the day_of_week date part to the date LookML, and to the table. Then created a custom calculation that was just:

if ( ${order.created_day_of_week} = "Saturday" OR ${order.created_day_of_week} = "Sunday", "Weekend", "" )

If I try to do pivot where this field = “Monday”, it returns null. 

 

pivot_where(${calculation_1}="Monday",${v_sec_cdrs.interaction_count})

 

Calculation 1 being the change from date to weekday

 

 
 

ryan.dunlavy:

 

 

 

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)

)

 

 

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?

 

index(

list("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"),

mod(diff_days(date(1900,01,01), ${dw_activity.completed_on_date}), 7)+1

)

 

I used this instead, the +1 should be outside of the mod so it forces to start at 1 and not 0. However, i found that starting at 2008 did not make the date accurate vs starting at 1900, unsure why but it would be one day off. 

Reply