Templated filter to get the max date in Looker Look ML

[Shan25]

Dear All,

Would like to hear your thoughts on one of the requirements we're attempting to do in Looker.

Sales and inventory are our two data sets, and they represent two different views. Using Union, we combined these two views to create the derived table. The calendar date is a filter, and both views inside the derived table must become filtered based on the dates that the user inputs. We were able to accomplish this by utilizing a templated filter as per below.

 {% condition filter_name %} sql_or_lookml_reference {% endcondition %}

Now our requirement is slightly different for the Inventory view, it should take the max date from the user input date range and provide the inventory stock.

E.g Example: If today is 4th Aug, then the SOH/SIT measure’s value on 3rd Aug should be displayed. If the last month is selected then Measure should show the values for the last day of that month.  

Please provide your valuable input.

Thank you.

[andy4]

Hello Shan! Knowing when/how to design liquid patterns can be tricky. Here's a quick rule of thumb I use to know right away whether liquid is the right path to my solution. 

1. First, identify what it is that you want to add to your SQL query. In this case it would be a WHERE clause on the 3rd of August, which is the max value of some date column in the inventory table.
2. Second, decide whether you could "know" that information before running a query. Some things you could know "before the query is run" include info in the filters pane, something like "what day is it today," which explore you're using, whether a specific field is in the query, etc.
3. If you cannot know the info from #1 "before the query is run," you cannot use Liquid. Instead, think of a subquery you could design and use instead

In your case, you cannot know the max value of the inventory date field before running a query. For this, design a subquery that is able to get that max(inventory_date), then create a new yesno dimension which compares that result to the inventory_date dimension, that will return "Yes" when the condition is satisfied. Since I love NDTs, I'm going to take this opportunity to show you how to use them (but you could do this with a SQL derived table as well)

view: max_inventory_date {
  derived_table: {
    explore_source: inventory_explore {
      column: max_inventory_date {field: inventory_view.max_inventory_date}
    }
  }
dimension: max_inventory_date {
  type: date
  sql: ${TABLE}.max_inventory_date
}
dimension: is_max_inventory_date {
  type: yesno
  sql: ${inventory_view.inventory_date} = ${max_inventory_date}
}
}

If you join that derived table into your inventory_explore and filter on the is_max_inventory_date field then that should do it! Except one more detail. You mentioned wanting to show the max date from the date range the end user is interested in. That would require passing your end user's date range filter into the subquery. With NDTs, you do that with a bind_filter. Here's what to modify (the two dimensions would be unchanged so I won't write them here again):

 explore_source: inventory_explore {
      column: max_inventory_date {field: inventory_view.max_inventory_date}
      bind_filter: {
        from_field: inventory_view.inventory_date #the field your end user is filtering on
        to_field: inventory_view.inventory_date #the field in your subquery you want to filter with the same user input
      }
    }

The bind_filter feature is the NDT analog to the templated filter design for SQL Derived Tables (the liquid you mentioned in your post). (tangent alert...) One thing that is different, is with Native DTs you can pass any user-input filter to the same field in the subquery with the bind_all_filters: yes feature. Basically it is like putting a bind_filter on every single field in an explore, where the to_field matches that from_field. This only works when your NDT is joined into the same explore as is listed in its explore_source. (...end tangent)

One more thing. That yesno field (is_max_inventory_date) can be used in your final query, but from the sounds of your question you might have meant it more for the SOH/SIT measure only. In that case, a measure-level filter would be more appropriate, since that would apply only to that one measure and not the entire query. Like this:

measure: stock_on_hand {
  description: "Total stock on hand as of the last day in the window of dates filtered on the Inventory Date field"
  type: sum
  sql: ${stock_count} ;;
  filters: [is_max_inventory_date: "Yes"]
}

Hope this helps!