LinkedIn
Twitter
Knowledge Drop
Last tested: Mar 25, 2020
Context: Pretty specific use case here, as the user wanted a way to compare data from each complete day so far this week, so if today were Wednesday, we wanted to see Sunday --> Tuesday. We wanted to compare that with Sunday --> Tuesday of last week, and also of the same current week, but last year. We accomplished this with a custom filter.
Janky Workaround for Week to Date for This Week Compared to Last Year:
mod(diff_days(date(2008,01,01), now()) + 1, 7) >= ${mysql_orders.created_day_of_week_index}
AND (ceiling(diff_days(date(extract_years(now()),01,01), add_days(-1 * mod(diff_days(date(2008,01,01), now()) + 1, 7) + 3, now()))/7) = ceiling(diff_days(date(extract_years(${mysql_orders.created_date}),01,01),add_days(-1 * mod(diff_days(date(2008,01,01), ${mysql_orders.created_date}) + 1, 7) + 3, ${mysql_orders.created_date}))/7) )
AND (extract_years(${mysql_orders.created_date}) = extract_years(now()) OR extract_years(${mysql_orders.created_date}) = extract_years(now()) - 1)
Janky Workaround for Week to Date for This Week and Previous Week Compared to Last Year:
mod(diff_days(date(2008,01,01), now()) + 1, 7) >= ${mysql_orders.created_day_of_week_index}
AND (ceiling(diff_days(date(extract_years(now()),01,01),add_days(-1 * mod(diff_days(date(2008,01,01), now()) + 1, 7) + 3, now()))/7) = ceiling(diff_days(date(extract_years(${mysql_orders.created_date}),01,01),add_days(-1 * mod(diff_days(date(2008,01,01), ${mysql_orders.created_date}) + 1, 7) + 3, ${mysql_orders.created_date}))/7)
OR ceiling(diff_days(date(extract_years(now()),01,01),add_days(-1 * mod(diff_days(date(2008,01,01), now()) + 1, 7) + 3, now()))/7) = ceiling(diff_days(date(extract_years(${mysql_orders.created_date}),01,01),add_days(-1 * mod(diff_days(date(2008,01,01), ${mysql_orders.created_date}) + 1, 7) + 3, ${mysql_orders.created_date}))/7) + 1)
AND (extract_years(${mysql_orders.created_date}) = extract_years(now()) - 1 OR extract_years(${mysql_orders.created_date}) = extract_years(now()) - 2)
Example Result:
Explanation:
day of week for now >= day of week for the date field<-- This is the week to date part that dynamically adapts to how far in the week we should checkAND(week of year for now = week of year for the date field OR week of year for now = week of year for the date field + 1)<-- +1 means we are getting results from the previous week)AND(year for now = year for date field OR year for now = year for date field - 1)<-- Get results for the previous year; the way the parentheses are means that the week to date part will always happen, then we can mix and match the other two conditions in any order.
Note: The first and last week of the year may not function as expected due to the nature of this calculation.
This content is subject to limited support.