Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Month to Date compared Month on Month

  • 17 May 2018
  • 4 replies
  • 1858 views
W

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

4 replies

rustyemmert

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.

N

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

Cyril_MTL_Analytics
Userlevel 5
Badge

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):

image



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

Dawid
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

    sql: CURRENT_TIMESTAMP ;;

    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

    sql: (EXTRACT(HOUR FROM CURRENT_TIMESTAMP) * 60 * 60) + (EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) * 60) + EXTRACT(SECOND FROM CURRENT_TIMESTAMP) ;;

  }



  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"

    sql: (${second_of_day} + ((TIMESTAMP_DIFF(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, DAY), TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, QUARTER), DAY) - 1) * 60 * 60 * 24)) ;;

  }



  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

Dawid

Reply


Terms of Service