This blog post will show you how to quickly create two dynamic fields to inject some interactivity into your Looker explores and content, thus improving the user experience of your Looker instance.
Looker is a powerful data analytics tool that allows you to explore and visualize your data in a flexible and intuitive way. One of the key features of Looker is its ability to create custom calculations and text fields using Looker’s Liquid syntax. In this blog post, we’ll explore how to create a dynamic field in Looker and provide a use case and examples.
What is a Dynamic Field in Looker?
A dynamic field in Looker is a field that is calculated at runtime using Looker’s Liquid syntax. Dynamic fields can be used to create custom calculations or text fields that are not included in the original dataset. By creating dynamic fields, you can add new dimensions or metrics to your data that are specific to your analysis or reporting needs.
Dynamic fields can be created in any Explore in Looker and can be used in your Looker visualizations just like any other field. However, it’s important to note that dynamic fields are calculated at runtime and may impact the performance of your Looker queries.
Looker’s Liquid Syntax
Before we dive into creating a dynamic field, it’s important to understand Looker’s Liquid syntax. Liquid is a templating language that allows you to create dynamic content in Looker. Liquid uses a series of tags, filters, and objects to manipulate data and create custom calculations and text fields.
Here’s a quick overview of some of the key elements of Liquid:
- Tags: Tags are used to create control flow statements in Liquid. Tags begin with “{% ” and end with ” %}” and can be used to create conditional statements, loops, and more.
- Filters: Filters are used to modify data in Liquid. Filters are applied using the pipe “|” character and can be used to format dates, perform calculations, and more.
- Objects: Objects are used to access data in Liquid. Objects are enclosed in double curly braces “{{ }}” and can be used to access fields, parameters, and more.
Step 1: Create the Parameters
We need to define two parameters to capture the user’s selections: one for the measure selection and another for the timeframe selection. Setting up the parameters involves creating a list of values that includes user-friendly labels and corresponding values in LookML. This allows users to make their selections from the provided options.
parameter: measure_selector {
type: unquoted
default_value: "revenue"
allowed_value: {
label: "Order Count"
value: "count"
}
allowed_value: {
label: "Revenue"
value: "revenue"
}
}parameter: timeframe_selector {
type: unquoted
default_value: "week"
allowed_value: {
label: "Day"
value: "date"
}
allowed_value: {
label: "Week"
value: "week"
}
allowed_value: {
label: "Month"
value: "month"
}
allowed_value: {
label: "Year"
value: "year"
}
}Step 2: Create the Dynamic Fields
Next, we’ll create “dummy” fields whose underlying fields in the table will be determined by the user’s input which, as we explained in Step 1, will be captured by the parameter.
All of the magic happens in the sql parameter. By using some liquid templating language, we can build the logic that will point at the correct measure or timeframe, depending on the value of the parameter.
measure: dynamic_measure {
label_from_parameter: measure_selector
type: number
sql:
{% if measure_selector._parameter_value == "count" %} ${count}
{% else %} ${total_sale_price}
{% endif %};;
}dimension: dynamic_timeframe {
label_from_parameter: timeframe_selector
sql:
{% if timeframe_selector._parameter_value == "date" %} ${created_date}
{% elsif timeframe_selector._parameter_value == "month" %} ${created_month}
{% elsif timeframe_selector._parameter_value == "year" %} ${created_year}
{% else %} ${created_week}
{% endif %}
;;
}Step 3: Set up dynamic number formatting
we’ll make to the measure is to dynamically set a value format, based on the parameter value selected by the user. Once again, we’ll use some liquid templating language to make the html parameter dynamic.
measure: dynamic_measure {
label_from_parameter: measure_selector
type: number
sql:
{% if measure_selector._parameter_value == "count" %} ${count}
{% else %} ${total_sale_price}
{% endif %};;
html:
{% if measure_selector._parameter_value == "revenue" %} {{ total_sale_price._rendered_value }}
{% else %} {{ count._rendered_value }}
{% endif %} ;;
}With this dynamic formatting in place, the values of the “Revenue” measure will appear in USD, while the “Count” measure will remain as an integer (at least in the result set and the tooltips in the visualization). Just make sure you set value_format parameters for ${total_sale_price} (no need to set one for ${count}).
Use Case: Concatenating First and Last Name Fields
Let’s explore a use case for creating a dynamic field in Looker. Imagine you have a dataset that includes a “first_name” field and a “last_name” field, but you want to create a new field that concatenates the two fields into a single “full_name” field. You could use a dynamic field to create this new field on the fly, without having to modify the original dataset.
Creating a Dynamic Field in Looker
Now that we understand Looker’s Liquid syntax, let’s walk through the steps to create a dynamic field in Looker.
Step 1: Navigate to the Explore where you want to create the dynamic field.
First, navigate to the Explore where you want to create the dynamic field. In this example, we’ll use the “users” Explore, which contains a “first_name” field and a “last_name” field.
Step 2: Click on the “Fields” tab.
Next, click on the “Fields” tab to view the list of fields in the Explore.
Step 3: Click on the “Add” button to create a new field.
Click on the “Add” button to create a new field.
Step 4: Give the field a name and select the “Advanced” option under the “Type” dropdown.
Give the new field a name, such as “full_name”, and select the “Advanced” option under the “Type” dropdown.
Step 5: In the “Definition” section, use Liquid syntax to concatenate the “first_name” and “last_name” fields.
In the “Definition” section, use Liquid syntax to concatenate the “first_name” and “last_name” fields. Here’s an example of what the syntax would look like:
{{ first_name }} {{ last_name }}
This syntax uses the double curly braces to access the “first_name” and “last_name” fields and the space between the fields to concatenate them into a single “full_name” field.
Step 6: Click “Save” to create the new dynamic field.
Click “Save” to create the new dynamic field. The new “full_name” field should now appear in the list of fields in Explore.
Conclusion
Creating dynamic fields in Looker using Liquid syntax is a powerful way to add new dimensions or metrics to your data without having to modify the original dataset. By using Liquid syntax to create custom calculations and text fields, you can unlock new insights from your data and create more flexible and intuitive visual
_________________________________________________________________________________________
More Imp Topics
How to calculate percent of total in looker?
What are Derived tables in Looker?
How to set conditional formatting in LOOKER?
Looker Interview Questions And Answers
New Looker Performance Recommendations Dashboard
Looker Git Version Control: How To Revert To A Specific Commit In Looker, No Git Commands Necessary
________________________________________________________________________________________
About Me:-
I am Om Prakash Singh – Data Analytics Consultant, Looker Consultant, and Solution Architect.
I am a Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation, and visualization. Furnish insights, analytics, and business intelligence used to advance opportunity identification.
You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results.
Reach out to us if you are interested to evaluate if Looker is right for you or any other BI solution.
