Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

YTD, MTD, QTD in matches advanced patterns

  • 16 August 2017
  • 5 replies
  • 3539 views
M
Userlevel 2

Is there a date filter “matches (advanced)” syntax for YTD, QTD, MTD ASOF/UP TO a specific date? An example would be: “YTD to 2017/05/01”…



I know you can provide a specific range like “2017/01/01 to 2017/05/01” but shorthand for YTD, QTD, MTD periods would be very helpful.

5 replies

rufus
Rank
Userlevel 3

@mplooker all of the relative date filters that are currently possible are listed here. We don’t currently have ytd, qtd or mtd as expressions but entering 'this year', 'this quarter' or 'this month' when dimension fill is off would return the equivalent result set.




To get year to date up to a certain date we can do 'this year to 2017-05-01'

la5rocks
Userlevel 2

I took the route of providing a dedicated filter (custom_to_date) for users to be able to provide any given date for a to-date calculation. We often need to-date reporting outside of the context of the current date.




Since the filter is a date, and the user might specify a range, we look for the last date with a date dimension table, and get the date part for filtering. Here I show MTD, but year, quarter, week, etc. are all very similar. ${day} would be the day of the month of whatever date you’re comparing, in your case.




I believe there’s an undocumented ability in Looker to pull just part of a date filter, and that will be simpler to use, especially if you don’t have a date dimension table.




filter: custom_to_date {



type: date



default_value: “today”



description: “Used for Custom To-Date calculations. The last date available in this filter will be used in all Custom To-Date measures.”



}




dimension: custom_mtd {



description: “Is up to the day of the month of the last date in Custom To Date”



type: yesno



sql: ${day} <= extract(day from (select max(sql_date) from date_dim where {%condition custom_to_date%} sql_date {%endcondition%})) ;;



group_label: “Month”



}

M
Userlevel 2

thanks @la5rocks! this is very similar to the solution we came up with as well. @rufus would be interested to hear if there are any plans to facilitate custom “matches (advanced)” filter expression patterns and sql-layer translations… thanks guys!

babublacksheep

@izzy any plans on adding MTD capabilities based on the date filters applied.



For example, if am filtering on 20th June and my date filter says 3 months, we would need MTD values in April, May & June

chriswragge
Userlevel 3

Surprised that

this year to yesterday

doesn’t work. Have a need to see YTD, but exclude the current day (relative) which has only partial data within it and causes all my charts to drop suddenly. 

Reply


Terms of Service