Filter a result by its relative rank with neighbors

Sorry for the late reply Josh! If you haven’t figured this out already,I think you could do this with some subqueries in SQL. Not sure if there’s a Looker magic spell here that will make things that much simpler, but filters sure are nice.

It’s not too complex as a derived table. I used a movies dataset we’ve got:

view: rank_dt {

  derived_table: {

    sql: 

    WITH ranks AS (SELECT 

	movies.title  AS title,

	SUM(movies.revenue) AS revenue,

	RANK() OVER (ORDER BY SUM(movies.revenue) DESC) AS rank

FROM mak_movies.movies  AS movies

GROUP BY 1

ORDER BY 2 DESC)



SELECT * FROM ranks

WHERE {% if title_filter._is_filtered %} 

rank <= (SELECT rank FROM ranks WHERE title = {% parameter title_filter %}) + 5 AND rank >= (SELECT rank FROM ranks WHERE title = {% parameter title_filter %}) -5

      {% else %}

      1=1

      {% endif %};;

  }

  

  filter: title_filter {

    type: string

  }

  

  dimension: movie {

    type: string

    sql: ${TABLE}.title ;;

  }



  dimension: rank {

    type: number

    sql: ${TABLE}.rank ;;

  }

  

  measure: revenue {

    type: sum

    sql: ${TABLE}.revenue ;;

  }



 }