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

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!

7 15 4,843
15 REPLIES 15

bens1
Participant V

This is a great idea! Looking forward to replacing a lot of Liquid with this simple code.

Dawid
Participant V

Is this part of the aggregate awareness? The ability to create view/derived table from an existing explore?

No, bind_all_filters is not part of aggregate awareness (the Looker feature in development); if you had to label this, bind_all_filters is more like filter awareness 😎. Aggregate awareness will use native derived tables (NDTs) and currently you can create NDTs from your explore.

bens1
Participant V

Just a thought - sometimes you want to bind, but not always (for example comparing two cohorts with different date ranges). Today, that requires building one join with bind filters applied, and another without. It would be amazing one day to have a field that was like a “bind_filters” toggle. Essentially, doing that would surface some conditional join flexibility for end users.

I have a caveat below, but I couldn’t resist the challenge. You could accomplish that with liquid. You’d need to create three views. One NDT with the bind, one without, and one view “to rule them all.” Your third view would have a liquid operation that would select * from ${ndt_1.SQL_TABLE_NAME} or ${ndt2.SQL_TABLE_NAME} depending on a user’s input against a parameter field.

But you should consider your end users here. Toggling bind filters on and off sounds straightforward, but try explaining the effect to someone who isn’t a LookML developer or doesn’t speak SQL. Probably best to create another explore and clearly state the purpose of both in their labels and descriptions.

So a question on this: is it possible to bind_all_filters except one?
I want to exclude a date dimension from the list specifically 

IanT
Participant V

I have implemented something similar but wanted to make everything dynamic (the dimension you rank over and the measure you rank by).

So far with a bit of quick coding I have got stuck with the dimension, what I have done is I created a param with a list of dimensions and then the dynamic dimension itself in the driving table of the explore:

  parameter: dynamic_rankby_dimension_selection {
view_label: "Top N Ranking"
description: "Specify which Dimension to rank by"
type: string
default_value: "Country"
allowed_value: {
label: "Country"
value: "Country"
}
allowed_value: {
label: "something else"
value: "something else"
}
}

dimension: dynamic_rankby_dimension {
label_from_parameter: dynamic_rankby_dimension_selection
description: "Set the dynamic rank by filter to the dimension of your choice"
sql:
CASE
WHEN {% parameter dynamic_rankby_dimension_selection %} = 'Country' THEN
${d_country.countryname}
ELSE
NULL
END ;;
}

This is fine and works upon testing when not using the NDT or ranking stuff.

Next I put the dynamic_rankby_dimension dimension into the NDT (this I have also tested by getting Looker to generate the LookML from exploring and it results in….):

...
        bind_all_filters: yes
        column: dynamic_rankby_dimension {}
...

Now when the NDT is joined into the explore the frontend gets upset (even though the NDT is not called upon and wont even appear in the query which confuses me). It will not show me the SQL but its telling me:

A LookML model issue prevented this query from running.

Unknown or inaccessible field "ndt_topx_rank.countryname" referenced in "ndt_topx_rank".

Is what I am trying to do impossible because of the order in which things take place such as modelling the NDT and then the user selecting the parameter value?

Any ideas on how I can achieve topX with a dynamic dimension to rank over?

Thanks!

IanT
Participant V

Ignore the above, I got this working, was a some leftover code from before I made it dynamic and then had to fiddle a bit with the datatypes coming from the ndt.

Beto
Participant II

@IanT Hello Ian, 

Thanks for that response, I'm still quite new to looker and I have the need for an implementation just like yours. I have a dynamic dimension with even some dynamic measures and I need to build a Top 10 , Would you mind sharing an anonymized version of your code ?? thank you so much.

IanT
Participant V

having troubles posting all that code, working through it

IanT
Participant V

pm sent @Beto 

May I also get a copy @IanT ? Thanks! Trying to implement something similar as well.

No, bind_all_filters is not part of aggregate awareness (the Looker feature in development); if you had to label this, bind_all_filters is more like filter awareness ?. Aggregate awareness will use  native derived tables (NDTs) and currently you can create NDTs from your explore.

This helped me, too! :thumbsup_tone5:

thomasrose
Participant I

Hey @IanT i would really love to also get a copy of your lookml! Sound like exactly what i need 😄

Hey @IanT

Hope you are well.

May I get a copy of your LOOKML code as well for me to study?

thanks

Top Labels in this Space
Top Solution Authors