View the original community article here
Last tested: Sep 6, 2019
The Problem
Quite frequently, a sea of values need to be displayed in a visualization to capture the whole story, such as company growth over time and how different products compare to one another. Sometimes, to answer a specific question, a particular value needs to stand out from the rest. If the value is static, we can always use the filter parameter in the LookML; however, if we want the users to be able to edit the filter to their particular question, we need to use a parameter and a duplicate field.
The Solution
The process is a variation of our doc on using Liquid Parameters
- Create a parameter in the view. https://docs.looker.com/reference/field-params/parameter
- The parameter will function if allowed_value is not added.
- If the user still wants to auto-populate the parameter, but has a massive amount of allowed values, they can instead use the suggest_dimension parameter to achieve the same functionality.
- Warning: If you will be working in an explore where this view is not the base view, you will also need to add the suggest_explore parameter referencing an explore where this is the base view.
Why are filter suggestions not populating?
- Create a duplicate field (in this example a dimension) that will return the value entered in the parameter or a null value.
- In the sql statement, create a CASE statement referencing the parameter.
sql: CASE
WHEN ${original_field} = {% parameter name_of_parameter %} THEN {original_field}
ELSE null
END ;;- The ELSE value can also be something generic such as "Other Values"
- Warning: If your original field has the link parameter, this CASE statement will get a bit wonky. In that case, reference the original column in the source table.
- In the sql statement, create a CASE statement referencing the parameter.
- Add the parameter and new field to your Explore query/tile edit modal.
- Pivot on the duplicated dimension.
- If applicable to your visualization (ex: scatterplot, line vis), in the Visualization Edit modal, turn off "Plot Null Values"
- Edit the visualization as desired!
- Define color values for your series in the customization section.
- If you used the "ELSE null", customize the series to display the preferred name.
This content is subject to limited support.