Dynamic Parameter for Dashboard Tile?

[alannavalh]

I am trying to build a tile for a dashboard that inherits a region dimension from the filter.

Basically, the tile needs to show: 
- All products that have $0 spend in the region from the filter 
- The total spend for each of the products.

Does anyone have experience building such a tile?

[looker_ant · Accepted solution]

Hey There!

Assuming your question is about Looker and not Looker Studio - here's a way you can solve this using parameters in LookML.

Normally if you want to pull a list of products where sales = 0 in a given region, you would just select the fields you need, add a dimension filter for Region = (my region of interest) and a measure filter for Sales = 0, like so:

The problem arises when you want to compare that regions $0 sales amount to the total sales of that product outside of that region. Because the Filter for region applies to the WHERE clause of the entire SQL query it makes it difficult to compare sales for one region all the others in a single report.

One way in SQL to accomplish comparing one regions sales to the rest of the sales is to use a case when statement inside of the SUM, like so:

SUM ( CASE WHEN region = [region_of_interest] THEN sale_price ELSE NULL END)

What we need to do is allow a User's input in a filter to into that [region_of_interest] portion of the SQL that Looker generates.Well Luckily Parameters in Looker allow us to do exactly this. 

A parameter in Looker allows us to create filter-only-field that a Dashboard or Explore user can interact with - but then we can dictate where that input goes into our LookML (and SQL)

Here's the LookML for the parameter that allows a user to select a region:

  parameter: region_selector {
    view_label: "Z) Dynamic Parameter for Dashboard Tile"
    type: string
    suggest_dimension: region
  }

Notice I'm using suggest_dimension here to give the user region suggestions from my already existing dimension for region. You could instead hard code the values by using allowed_values 

This Parameter shows up in the explore UI like this:

Next we want to create a helper dimension that checks if the given transaction's region matches the region of interest the the use selected in the filter above: 

  dimension: is_selected_region {
    view_label: "Z) Dynamic Parameter for Dashboard Tile"
    type: yesno
    sql: ${region} = {% parameter region_selector %} ;;
  }

Notice the use of {% parameter region_selector %} - this is a callback to our parameter from earlier and allows us to inject the user's input from the dashboard to anywhere we like in the LookML/SQL

Last LookML change, we are going to create a filtered measure that only sums when this helper dimension evaluates to "Yes": 

  measure: total_sales_for_region {
    view_label: "Z) Dynamic Parameter for Dashboard Tile"
    type: sum
    sql: ${order_items.sale_price} ;;
    filters: [is_selected_region: "Yes"]
  }

Finally in the Explore (or Dashboard) we can select SKU/item name, our region selector filter only field, our new filtered measure (and filter to = 0), and our old non-filtered sales measure to make the comparison you are looking for:

Here is all the LookML put together for convenience:

  parameter: region_selector {
    view_label: "Z) Dynamic Parameter for Dashboard Tile"
    type: string
    suggest_dimension: region
  }
  
  
  dimension: is_selected_region {
    view_label: "Z) Dynamic Parameter for Dashboard Tile"
    type: yesno
    sql: ${region} = {% parameter region_selector %} ;;
  }
  
  measure: total_sales_for_region {
    view_label: "Z) Dynamic Parameter for Dashboard Tile"
    type: sum
    sql: ${order_items.sale_price} ;;
    filters: [is_selected_region: "Yes"]
  }

Thanks for your question! Let me know if you have any follow up questions and if this solved your question please mark this as solved!

[alannavalh]

This was explained so beautifully, I was able to implement in minutes. Thank you so much.

[looker_ant]

Thank you! Happy I could help