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)
LinkedIn
Twitter
