Looker will not be updating this content, nor guarantees that everything is up-to-date.
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.
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.
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:
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())
.
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.
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 ;;
}
Theday_of_week_index
will vary, depending on your SQL dialect. For example, Redshift'sday_of_week_index
is formatted asSunday=0
,Monday=1
, and so on, which is different from MySQL.
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.