Month to Date and Year to Date Analysis

[Zam]

Month-to-Date (MTD) and Year-to-Date (YTD) analyses are useful when conducting timeframe-based comparisons.

For example, while you are part of the way through a month, you may want to compare your progress with earlier months. However, it would not be useful to compare part of the current month with the entirety of earlier months. Instead, it would make more sense to only look at the days in earlier months that have already transpired in the current month.

The best way to execute this type of analysis in Looker is to create a type: yesno dimension that allows you to limit the query to days that have already passed in the given time frame. Getting this to work properly in a variety of scenarios takes some clever SQL and Looker usage.

MTD and YTD Analysis in Looker

Let’s assume that the dimension group on which we want to perform these analyses looks like this:

- dimension_group: created
  type: time
  timeframes: [time, date, week, year]
  sql: ${TABLE}.created_at

Our goal is to create a dimension of type: yesno that will return yes if the date is anytime before today, and no otherwise. We will have to base our dimension on the time frame in which we are interested. Let’s consider MTD to begin with, then we can easily convert this to YTD with some simple replacements.

MySQL / Postgres is_before_mtd

- dimension: is_before_mtd
  type: yesno
  sql: |
    (EXTRACT(DAY FROM ${created_time}) < EXTRACT(DAY FROM CURRENT_TIMESTAMP)
      OR
      (
        EXTRACT(DAY FROM ${created_time}) = EXTRACT(DAY FROM CURRENT_TIMESTAMP) AND
        EXTRACT(HOUR FROM ${created_time}) < EXTRACT(HOUR FROM CURRENT_TIMESTAMP)
      )
      OR
      (
        EXTRACT(DAY FROM ${created_time}) = EXTRACT(DAY FROM CURRENT_TIMESTAMP) AND
        EXTRACT(HOUR FROM ${created_time}) <= EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AND
        EXTRACT(MINUTE FROM ${created_time}) < EXTRACT(MINUTE FROM CURRENT_TIMESTAMP)
      )
    )

Redshift is_before_mtd

- dimension: is_before_mtd
  type: yesno
  sql: |
    (EXTRACT(DAY FROM ${TABLE}.created_at) < EXTRACT(DAY FROM GETDATE())
      OR
      (
        EXTRACT(DAY FROM ${TABLE}.created_at) = EXTRACT(DAY FROM GETDATE()) AND
        EXTRACT(HOUR FROM ${TABLE}.created_at) < EXTRACT(HOUR FROM GETDATE())
      )
      OR
      (
        EXTRACT(DAY FROM ${TABLE}.created_at) = EXTRACT(DAY FROM GETDATE()) AND
        EXTRACT(HOUR FROM ${TABLE}.created_at) <= EXTRACT(HOUR FROM GETDATE()) AND
        EXTRACT(MINUTE FROM ${TABLE}.created_at) < EXTRACT(MINUTE FROM GETDATE())
      )
    )

MS SQL is_before_mtd

- dimension: is_before_mtd
  type: yesno
  sql: |
    (DATEPART(DAY, ${created_time}) < DATEPART(DAY, CURRENT_TIMESTAMP)
      OR
      (
        DATEPART(DAY, ${created_time}) = DATEPART(DAY, CURRENT_TIMESTAMP) AND
        DATEPART(HOUR, ${created_time}) < DATEPART(HOUR, CURRENT_TIMESTAMP)
      )
      OR
      (
        DATEPART(DAY, ${created_time}) = DATEPART(DAY, CURRENT_TIMESTAMP) AND
        DATEPART(HOUR, ${created_time}) <= DATEPART(HOUR, CURRENT_TIMESTAMP) AND
        DATEPART(MINUTE, ${created_time}) < DATEPART(MINUTE, CURRENT_TIMESTAMP)
      )
    )

##Adjusting for YTD

To do the analysis on YTD instead of MTD, we simply have to extract a different part of the date dimension. Depending on which dialect we’re in, we may need to change syntax.

Replacements by Dialect

# MySQL 
# from:
EXTRACT(DAY FROM [date])
# to: 
DAYOFYEAR([date])

#Postgres / Redshift
# from
EXTRACT(DAY FROM [date])
# to
EXTRACT(DOY FROM [date])

#MS SQL
# from
DATEPART(DAY , [date])
# to
DATEPART(dayofyear , [date])

Creating the Query

To execute the YTD or MTD analysis, simply select your Is Before YTD/MTD dimension as a filter and set it to yes.

Example

In this example we’re using:

• ORDERS Created Month as a dimension
• ORDERS Count, ORDERS Total Revenue, and ORDERS New Customer Revenue as measures
• ORDERS Is Before Mtd = Yes as a filter

Now we can get an apples-to-apples comparison of these metrics between the current month and previous months.

MTD Total Order Comparison