Week-to-Date (WTD) Comparison

(Paola) #1

Week-to-Date (WTD) comparison

Let’s say we would like to compare orders we’ve received this week to date versus orders from previous weeks to the same day of the week. For example, if today were Thursday, we could compare the orders we’ve had this week to the orders from previous weeks Monday-Thursday.

Our approach here will be to create a dimension of type yesno which will grab only days of week equal to or before today. We can then filter on that dimension is yes to dynamically get only the relevant days of week.

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, etc.

Let’s say our created order date dimension group looks like this:

New LookML ``` dimension_group: created { type: time timeframes: [date, month, day_of_week, day_of_week_index] sql: ${TABLE}.created ;; } ```
Old LookML ``` - 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 a yesno dimension that will give us the days of the week from Monday until Today.

The SQL 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, etc. 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 that are on and before today. We can get this by computing ${created_day_of_week_index} <= WEEKDAY(NOW()).

Next, we want to make sure we’re only grabbing the days of the week that are 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.

Now, we can put those two together and create our yesno dimension! It will return ‘Yes’ for all days of the week that are on and before today’s day of week.

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

Please note that 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, etc., which is different from MySQL.

Now we can compare Week-to-Date orders on the Explore page. Just 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”, 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!

1 Like

Year to Date Function
Hiding empty columns across broad date range
Calculating week to date values
(sam) #2

If you’re using Redshift, you can replace WEEKDAY(NOW()) with EXTRACT(dow from GETDATE()).

1 Like

(vincent) #3

And if you would like to do a Year to week comparison, here is the SQL and LookML(assuming MySQL):

  dimension: until_this_week {
    type: yesno
    sql: ${created_week_of_year} <= WEEK(NOW()) AND ${created_week_of_year} >= 0 ;;
  }

0 Likes

(Aleksandrs Vedernikovs) #4

If you’re Biqguery user, you can achieve week_to_date comparison with:

dimension: dow_is_before_today {
description: “Created Day of Week matches today or earlier”
type: yesno
sql: ${created_day_of_week_index} <= ${dow_index_today} AND ${created_day_of_week_index} >= 0 ;;
}

0 Likes

(Daniel James Spindler) #5

What would be the SQL code if I wanted to

Look at the previous month? (All the dates would need to be the last day of the month)

The end goal is I want to have a column that would show % improvement from last month.

MONTH__________ SCORE ____________ PERCENT Improvement
1-31_______________ 90%
2-28 _______________ 92%______________ 2%
3-31 88%_-4

0 Likes

(Chris Seymour) #6

Hey @dpsindler,

For filtering on the last day of the month, you can check out this discourse: [Retired] Filtering On Last Day of Month

For comparing each score to the previous month’s score, there are a couple of different approaches you could take. The simplest approach would be to use the offset function in a table calculation, as shown in the first example here: Using offset and pivot_offset in table calculations (3.18+)

However, if you prefer to do this in SQL, you could use a window function like LAG in a derived table, as shown in the second example here: Using Window Functions in Looker

0 Likes

Want to create Yesterday, WTD, MTD, QTD and YTD
(Daniel James Spindler) #7

Thank you for the resources!

0 Likes