Month to Date compared Month on Month

  • 17 May 2018
  • 4 replies

Hello helpful bunch

Another question about comparing different dates, this time looking to compare Month to Date to the previous month. We have a “Month to Yesterday” dimension, but I’m struggling to pull the same number of days for the previous month.

I was wondering whether there was a filter I could use which looks at the number of days gone by this month and then pulls in the same number of days in the previous month?

Any suggestions welcome!



4 replies

If you need something quick, you can include the day_of_month into your timeframes on the date dimension group, then use it as a filter. The downside of this is having to manually specify a number. But, you could do in the past 2 months on the month dimension, and less than 17 (or whatever today’s happens to be) on the day_of_month.

Otherwise, here’s something I just tried and seems to work:

  • Add day_of_month to your intended time dimension group

  • Create a dimension_group to wrap today’s time:

  dimension_group: hidden_today {
type: time
timeframes: [day_of_month]
hidden: yes
sql: getdate() ;;

  • Create a is_before_day_of_month filter (which you can test as a dimension first)

  filter: is_before_day_of_month {
type: yesno
sql: ${session_day_of_month} < ${hidden_today_day_of_month} ;;

Go to your explore. Put a filter on month for the past 2 months. Click your new filter-only field to exclude results that are before day.

Hope this helps!

PS: The motivation for the getdate() wrapper is to preserve whatever timezone logic you may have set up in Looker. But you can just do an extract day from getdate() style comparison if you’re not worried about that.

@Woody_Hennekam , would you mind sharing your “Month to Yesterday” dimension please?

Userlevel 5

We do that quite a lot for clients and here it is in essence:

  • Create a yesno dimension to compute the only display data where the date is between the first day of a month and the current day of the month

    dimension: month_to_date_agnostic {
    description: "true if the created date is between the first day of the month up to the current day of month. Does NOT manage the various number of days in months (28, 29, 30, 31)"
    type: yesno
    sql: ${created_day_of_month} <= date_part('day', current_date) ;;

  • In your Explore:

    • filter on the above dimension on “yes”

    • Select the month dimension and a measure

    • You’re done!

Here is an example that shows that the data stops after the 18 of the month (which the days of the month when I ran the report):

Important notes:

  • You’ll need the *_day_of_month timeframe in your base dimension_group

  • For yesterday, you can just add -1 to the sql part

Userlevel 7
Badge +1

If you happen to be unbelieavably lucky to have near real-time data, I use a view called current time that looks like this:

view: current_time {

dimension_group: current {
type: time
timeframes: [date, hour_of_day, minute, day_of_week, day_of_month, day_of_year, week_of_year, day_of_week_index, time_of_day, month, month_name, month_num, time, week, year, raw]

dimension: second_of_day {
group_label: "Current"
type: number

dimension: second_of_week {
group_label: "Current"
# Current day + full weekdays prior
sql: ${second_of_day} + ((COALESCE(NULLIF(EXTRACT(DAYOFWEEK FROM CURRENT_TIMESTAMP) - 1, 0), 7) - 1) * 60 * 60 * 24);;

dimension: second_of_month {
group_label: "Current"
sql: (${second_of_day} + ((EXTRACT(DAY FROM CURRENT_TIMESTAMP) - 1) * 60 * 60 * 24)) ;;

dimension: second_of_quarter {
group_label: "Current"

dimension: second_of_year {
group_label: "Current"
sql: (${second_of_day} + ((EXTRACT(DAYOFYEAR from CURRENT_TIMESTAMP) - 1) * 60 * 60 * 24)) ;;

It can be simpler but that depends on date functions in your dialect.

Then I join it to let’s say orders view and create yesno dimensions before second of day or before second of quarter