Dynamic Rank in Visualization Using Parameter

Looker Tips & Resources Technical Tips & Tricks
1

This article requires knowledge of Liquid variables — specifically, {% parameter parameter_name %}.

Using a LookML parameter called parameter, you can let users choose the top N ranking to display in visualizations on a dashboard. For example, if a user wanted to see the top seven product categories by total sale price, they could enter that value in a filter and the results would update dynamically:

The Process

To implement the above solution, we need to perform the following steps:

  1. Add a type: number dimension and parameter to the appropriate LookML view (typically the same view that contains the fields you would like to visualize into rankings). The parameter will surface as a front-end filter for users who want to specify the desired number for rank, and the dimension will reflect that input to control how many data points are displayed in the visualization. The user’s parameter value is captured with the {% parameter parameter_name %} syntax in the sql parameter of the type: number dimension:

    parameter: max_rank {
type: number
}

dimension: rank_limit {
type: number
sql: {% parameter max_rank %} ;;
}

  2. In an Explore, select the dimension you want to rank and the measure you want to sort by. In the following example, we have selected products.category to be sorted by order_items.total_sale_price to see which categories have the most sales:

  3. Next, add the max_rank parameter as an Explore filter, and the rank_limit dimension to the Explore table to group by:

  4. Next, create two table calculations to limit how many rows are displayed based on what the user has entered in the max_rank filter. The first table calculation, Rank, uses the rank function, which outputs a ranking for each value of order_items.total_sale_price in the query when comparing it to the entire column of order_items.total_sale_price. The second table calculation, show_in_visualization, is a simple statement that compares the rank table calculation value with the rank_limit dimension. This calculation outputs Boolean yes or no values, depending on whether a value meets the condition.

  5. Then, hide both table calculations and the rank_limit column from the visualization so that only the desired fields appear.

  6. To complete adding the rank_limit to the visualization, we use the Boolean output from the show_in_visualization table calculation to hide the values from the visualization that do not meet the condition. To do this, select Hide "No"s from Visualization from the show_in_visualization table calculation’s gear menu:

  7. Finally, add the visualization to a dashboard, and add a filter to the dashboard, linked to the max_rank parameter:

6 Likes