Knowledge Drop

# Week to Date over Week to Date Analysis

• Department of Customer Love • Looker Staff
• 0 replies

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.

This content is subject to limited support.