[Analytic Block] - Pivot by Top X - Introducing bind_all_filters: yes

[Bryan_Weber]

Pivot by Top 10

You’ve got a high cardinality dimension and you’d love to just see the Top 10 based on some measure. In our sample data, that’s the Product’s Item Name; and you’d love to see the Top 10 of your best performing items. Now that Top 10 list frequently changes, plus it would be nice to filter it down by other fields in my explore. If you’re familiar with templated filters in a derived table or with bind_filters in a native derived table, you might have done a version of this, but upkeep of those bind_filters and templated filters are burdensome every time your model changes. But in Looker 6.20, you can use bind_filters_all: yes to make sure you’re ALWAYS filtering down to the exact right top 10 based on what question is being asked.

Here’s a quick example:

Introducing bind_all_filters for native derived tables.

bind_all_filters: yes will listen to every filter in the explore and apply it into your native derived table. Want to see the Top 10 items in the Accessories Category, ordered on Sunday and delivered on Wednesday, for users that have ordered exactly 2 items? Piece of cake. bind_all_filters will apply all of those filters into your native derived table to calculate the top 10 on any filter from your explore. If you add a new dimension into your model or change a definition, if it’s filtered, your native derived table will update.

Here’s a quick example from my order_items explore, where I want to rank the Item Name by the Total Sale Price field. I will also use the calculated Rank for a new dimension in order to see the ranking on the labels.

view: top_10_simple_item_names {
  view_label: "Top 10s"
  derived_table: {
    explore_source: order_items {
      column: total_sale_price { field: order_items.total_sale_price }
      column: item_name { field: products.item_name }
      derived_column: rank { sql: RANK() OVER (ORDER BY total_sale_price DESC) ;;}
      bind_all_filters: yes
      sort: { field: total_sale_price desc: yes}
      timezone: "query_timezone"
      limit: 10
    }
  }
  dimension: item_name { group_label: "Simple Example"  }
  dimension: rank { type: number group_label: "Simple Example" }
  dimension: item_name_ranked {
    group_label: "Simple Example"
    order_by_field: rank
    type: string
    sql: ${rank} || ') ' || ${item_name} ;;
  }
} 

And the explore:

explore: order_items {
   ...
   join: top_10_simple_item_names {
    type: inner
    relationship: many_to_one
    sql_on: ${products.item_name} = ${top_10_simple_item_names.item_name} ;;
  }
}

Note:
A native derived table with bind_all_filters is required to be joined in the same explore it came from; i.e., explore = explore_source

We would love to hear how else you would use this new property; we know there’s tons of use cases for it!