Often when building dashboards where a user wants to be able to view certain metrics by different dimensions. For example, they may want to look at sales by State, County, City, and Store to see how sales are performing in various areas. One way to provide that information would be to create 4 tables – one for Sales by State, one for Sales by County, one for Sales by City, and one for Sales by Store. These four tables would take up enough space for one report, not leaving much room to analyze anything else.
In Looker we can create a dynamic dimension so that instead of having four separate tables, we can have one table and a filter that changes the dimension field. Let us walk you through an example of how to create that.
Goal: We would like to analyze Iowa liquor Sales Dollars, Liters Sold, and Bottles Sold by different location types – County, City, and Store.
Challenge: We don’t want to create three tables that are the same except for the first column. This would take up a lot of space in our report. In addition, the column header in the first column needs to change dynamically with a filter.
Solution: Create a dynamic dimension and allowing the user to change the location type in the report.
Note: The database used is Google BigQuery. Data Source: Google BigQuery bigquery-public-data – iowa_liquor_sales
Step 1: Create a parameter for the location type. Include all the options required as allowed values. Use the “label” parameter to ensure the case sensitivity is what you want it to be.
Step 2: Create a dimension to declare what fields to use with each allowed value.
- Use label_from_parameter to bring in the label declared in the parameter created in Step 1.
- Use liquid and your database’s appropriate syntax to write a CASE statement bringing in the applicable dimensions.
Step 3: Create a look using the dimension you just created, Location Type Dimension for Parameter. Save to a new or existing dashboard.
Step 4: Add the parameter you created as a filter to the dashboard. Rename to “Location Type” for readability.
Now we have a condensed report that allows us to filter the location dimension dynamically.
Now that we’ve learned how to create dynamic dimensions in Looker, what if we have the inverse problem and want to analyze different metrics by one dimension? In the next article, Creating Dynamic Measures in Looker, we’ll walk you through that.