How to Show [MM - DD] instead of [YYYY - MM - DD]

Want to compare revenue on the same day between 2017 and 2018. How can I just show MM-DD instead of YYYY-MM-DD?

LookML Question: What should I put under “timeframes”?

DIMENSIONS

dimension_group: PT {
type: time
timeframes: [date, day_of_week, day_of_month, week, month, month_name, month_num, quarter, year, day_of_year]
sql: ${TABLE}.pt_date ;;
}

We don’t currently have a specific timeframe for [MM-DD] but there are several ways to achieve this.

A simple way to show year over year comparisons is to use the day_of_year or day_of_month timeframes with a filter on month and a pivot on year:

If it’s necessary to display the dates as MM-DD we can do that by concatenating the month_num and day_of_month timeframes in a new dimension. (Adding the group_label makes it show up within the same date field in the field picker)

  dimension: pt_month_day {
    group_label: "PT Date"
    sql: ${PT_month_num} || '-' 
    || ${PT_day_of_month} ;;
  }

To get a comparison for a single day, we can filter on that new dimension and use an offset table calculation to show the year over year values:

Hope the examples help! I’ll be sure to let our product team know you’re interested in more native timeframe options.

1 Like

Thank you for the reply.

I copied and pasted your code in the LookML, and it returned an error stating that “Failed to retrieve data - Argument type mismatch in function LOGICAL_OR: first argument is type int64, ‘-’ is type string”

I was able to work around this using normal date in my table but displaying %m-%d in the X-axis to hide the year. But I would love to see a more native timeframe option.

Thanks.

Hey @Swang2, we’ve passed your feedback along to our product team! I wanted to mention that every dialect of SQL has different sytax, so the exact SQL that Rufus posted will not work in all cases. However, you should be able to do something similar using your dialect’s CONCAT function. Cheers!

Thanks for this guide.
I was quite surprised when I realized that this type of date representation wasn’t available in the default dimension_group.

Since this is really useful when comparing time periods across years I would suggest that you try to add as an default type in the time dimension_group

2 Likes