Week-related table calculation functions

Knowledge Drop

Last tested: Dec 2, 2019
 

Currently, there are no week-related table calculation functions available. Below are feature requests and workarounds for various week-related functions.

extract_day_of_week / extract_day_of_week_index

A function to extract the day of the week or day of week index from a date field doesn't currently exist - request it here.

Workarounds:

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

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


trunc_weeks

A function to get the week from a date field doesn't currently exist

Workaround:


add_days(-1 * mod(diff_days(date(2008,01,01), ${orders.created_date}) + 1, 7), ${orders.created_date})


Weekday / business day count

No function currently available natively, request it here.

Workaround:


diff_days(${start_date}, ${end_date}) + 3 - floor(diff_days(add_days(-1 * (mod(diff_days(date(2008,01,01), add_days(-6, ${start_date})) + 1, 7)), add_days(-6, ${start_date})), add_days(-1 * (mod(diff_days(date(2008,01,01), add_days(2, ${end_date})) + 1, 7)), add_days(2, ${end_date})))/7) - floor(diff_days(add_days(-1 * (mod(diff_days(date(2008,01,01), add_days(-7, ${start_date})) + 1, 7)), add_days(-7, ${start_date})), add_days(-1 * (mod(diff_days(date(2008,01,01), add_days(1, ${end_date})) + 1, 7)), add_days(1, ${end_date})))/7)

This content is subject to limited support.                

Comments

Keep in mind we can create the start_date and end_date with the min/max date columns table calcs here as well:
 

https://community.looker.com/explores-36/how-to-determine-the-minimum-or-maximum-date-with-table-cal...
Gerald1
Bronze 4
Bronze 4

It feels like an odd omission to not have week-based Looker functions. Our company relies heavily on weekly reporting, and so including something like trunc_week would be super useful. 

I tried to follow the “request it here” links, but I keep getting sent to a Pendo log-in page for which I have no credentials and cannot create an account. A bit frustrating ?

39d4f623-3144-47a0-981b-6cad2e7dea23.png
Version history
Last update:
‎05-07-2021 09:48 AM
Updated by: