Month to Date compared Month on Month

filter

(Woody Hennekam) #1

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!

Thanks,
Woody


(Russell) #2

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.