Question

Day of week in custom dimension?

  • 17 September 2018
  • 8 replies
  • 4391 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

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

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. 

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?

Reply