Looker will not be updating this content, nor guarantees that everything is up-to-date.
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.
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:
Here is what we can do to address these issues:
is_top_10
, create a country_rank
dimension we can filter on to get any arbitrary top N countries. In the example below, this dimension is called rank
.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 the WHERE
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 the filter
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:
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.
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.