The Problem

We want to compare the orders we have received this week, up to and including today, with the orders from previous weeks, up to and including the same day of the week.

The Solution

We can create a dimension of type: yesno, which will use only the days of the week equal to or before today. Then, we can filter on that dimension equal to yes to dynamically get only the days of the week we are interested in.

Example

If today is Thursday, we could compare the orders we have had this week, from Monday through Thursday, with the orders from the previous week, from Monday through Thursday.

The first step is to utilize Looker's day_of_week_index timeframe, which displays the days of the week as Monday=0, Tuesday=1, Wednesday=2, and so on.

We can create an order date dimension_group, which looks like this:

dimension_group: created {
  type: time
  timeframes: [date, month, day_of_week, day_of_week_index]
  sql: ${TABLE}.created ;;
}

Once we have our dimension_group specified, we can create the yesno dimension to give us the days of the week from Monday through today (Thursday).

The SQL code for this dimension will consist of two parts:

1. First, we want to figure out today's day_of_week_index:

   In this example, we'll be using MySQL's WEEKDAY() and NOW() functions, which will return today's day_of_week_index in the format Monday=0, Tuesday=1, and so on. If today is Thursday, then WEEKDAY(NOW()) would return 3. Once we have that, we want to compare today's index to the created_day_of_week_index. We want all the days of the week on and before today. We can get this by computing ${created_day_of_week_index} <= WEEKDAY(NOW()).

2. Get the correct days:

   Next, we want to make sure we are only grabbing the days of the week on and after Monday (since our week starts on Monday). We get this by adding ${created_day_of_week_index} >= 0 to our sql statement.

3. Put the code together:

   Now, we can put those two together and create our yesno dimension. It will return yes for all days of the week on and before today's day of the week.

   dimension: until_today {
     type: yesno
     sql: ${created_day_of_week_index} <= WEEKDAY(NOW()) AND ${created_day_of_week_index} >= 0 ;;
   }

   The day_of_week_index will vary, depending on your SQL dialect. For example, Redshift's day_of_week_index is formatted as Sunday=0, Monday=1, and so on, which is different from MySQL.

4. Compare Week-to-Date orders:

   Now, we can compare Week-to-Date orders on the Explore page. Make sure to filter on our until_today yesno dimension as is yes. In the example below, we filter on in the past 2 weeks.

   If we pivot on Created Week and include Created Day of Week and Orders Count, we can compare orders count per day.

Feel free to play with the different visualization types and the date filter to compare more weeks.