This article is about dynamically filtering dimensions based on an aggregation. You can use the examples from this article to filter a dimension by its relative ranking over any metric. The most common use case is filtering pivoted data by revenue (or other aggregation), rather than an alphanumeric string, in order to see only the top N pivoted values.
This analytical pattern results in a visualization such as the following, based on an e-commerce dataset:
Dynamic Rankings in Looker
For example, if we want to look at the number of users in each country, but want to filter the countries by the top 10 in revenue, we might think to build a is_top_10
dimension like this in our Orders view:
dimension: is_top_10 { type: yesno sql: exists( select * from ( select country from orders group by country order by sum(revenue) desc limit 10 ) top_10 where ${country} = top_10.country ) ;; }
This approach will work; however, it's not best practice for two reasons:
- The user can only look at the top 10, instead of dynamically querying the top N.
- The top 10 are being calculated over all time, which may not line up with filters created in the Explore section.
Here is what we can do to address these issues:
- Instead of
is_top_10
, create acountry_rank
dimension we can filter on to get any arbitrary top N countries. In the example below, this dimension is calledrank
. - Build the query in an ephemeral derived table, and create a
filter
field that will appear as a front end filter where users can input a date value. We can reference the value the user inputs into the filter in theWHERE
clause of the derived table to determine rankings using a different timeframe than the one being applied to the orders table. We do this by referencing thefilter
field in a templated filter. For example, we might want to filter down this dataset to output this year's top 10 countries, but ultimately filter the orders Explore only for the last month. We can achieve this with a dynamic derived table using templated filters.
Here is the code:
MySQL Example
view: country_revenue_ranks { derived_table: { sql: select country , cast(@curRank := @curRank + 1 as int) as rank from ( select country from orders where {% condition rank_date_filter %} orders.created_at {% endcondition %} group by country order by sum(revenue) desc) ordered_list, (select @curRank := 0);; } filter: rank_date_filter { type: date } dimension: country { primary_key: yes sql: ${TABLE}.country ;; } dimension: rank { type: number sql: ${TABLE}.rank ;; } }
NOTE: The solution for dialects with window functions (Redshift) would useROW_NUMBER()
instead of the@variable
iteration used above.
Postgres Example
derived_table: { sql: select country , sum(revenue) as duration , rank() over (order by sum(revenue) desc) as rank from orders where {% condition rank_date_filter %} orders.date {% endcondition %} group by 1 ;; } filter: rank_date_filter { type: date } dimension: country { primary_key: yes sql: ${TABLE}.country ;; } dimension: rank { type: number sql: ${TABLE}.rank ;; } }
For an example of implementing this analytic pattern using bind_all_filters
with a Native Derived Table, see this Community post.
NOTE: A derived table cannot be persisted if it makes use of a templated filter. There are potentially an infinite number of possible user inputs, so the number of persistent tables in your database could potentially become unmanageable.