Week to Date over Week to Date Analysis (Community)

View the original community article here

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 check
• AND
(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.