View the original community article here

Last tested: Aug 28, 2019
 

Liquid is an open-source, Ruby-based template language created by Shopify It can be used in conjunction with LookML to build a more flexible, dynamic code.

Liquid code is denoted by braces { } and falls into 3 different categories:

TAG:
{% %}
used to create logic

OBJECT:
{{ }}, {{ value }}
output pieces of data

FILTERS:
modify the output of numbers, strings, objects, and variables,
denoted by a pipe character |
{{ value | capitalize }} = VALUE

We can pass values through multiple filters at the same time to compute complex sums.
{{ product.sale_price | minus: product.price | times: 100.0 | divided_by: product.sale_price }}

Check out the following external links, there is some really good content here to get deeper into all the things Liquid can do. And handy to have for reference.
Cheat Sheet
Liquid Wiki
Conditional Logic

Where can I use Liquid in Looker?
 

There are several places in LookML where Liquid can be used:

The html parameter
The HTML parameter enables you to specify the HTML that will be contained by a field.

The link parameter
The link parameter makes it easier and more useful to add Looker links and other external links to your dimensions and measures

The action parameter
The action parameter creates a data action that lets users perform tasks in other tools

The label parameter of a field.

Parameters that begin with sql
sql, sql_on, sql_table_name

Referencing in Liquid
 

LookML Objects → value
Liquid Tags → {% parameter parameter_name %}
Referencing Special LookML → _user_attributes['name_of_attribute']
Referencing Values in a Query → View_name.field_name._is_filtered

sql_table_name:

{% if event.created_date._in_query %}

event_by_day

{% elsif event.created_week._in_query %}

event_by_week

{% else %}

event

{% endif %} ;;

A full list of the liquid parameters can be found in our docs
 

Use cases for Liquid in Looker?
 

Liquid can be used in a wide variety of ways in Looker. Some of the most popular use cases include:

Using User Attributes for Dynamic Schema and Table Name Injection
In this use case the user wants the table name to change depending on a user attribute.
Check out an example here

Creating dynamic links or rendering dynamic images
In this case the user might want to add images to a visualisation for each brand displayed instead of showing the brand name we can replace the names with images dynamically. Check out how to do this here

Changing the label of a field based on the x being used
Users sometimes want to have label names dynamically change depending on what model is been used or the explore, view, or field selected or a user attribute set for the user. Check out a cool example of this here

Adding custom conditional formatting
Sometimes users want to conditionally format the data cells with different fonts, images, colours and all dynamically based off values in the cells. A great example of this can be seen here
 

Use Cases in more detail
 

Drilling and linking in liquid
 

Custom Links and Drills are a common use case where we see customers use liquid to build workflows in Looker, They build custom links between dashboards and explores in order to set up custom workflows between Looker content or between Looker and other internal or external resources. Let's take a look and how we can use liquid and lookml to build links and drills between content.

Link Parameter
The link parameter makes it easier to add Looker links and other external links to your dimensions and measures. Links created using the link parameter appears when the field is clicked. Most links are added to dimensions and measures using the link parameter.

Within the Link Parameter.
label is the name this link will have in the drill menu.
url is the URL of the link. This parameter supports full liquid (but not full HTML).
icon_url is the URL of an image to be used as an icon for this link.
 

dimension: field_name {

link: {

label: "desired label name"

url: "desired_url"

icon_url: "url_of_an_image_file"

}

}

Absolute links (such as https://example.looker.com) will open in a new browser tab.
Relative links (such as /dashboards/456) will open in the same browser tab or iframe.

linking out to google, a dashboard and an explore.
 

In some cases, customers want to create a link out an external page or give the option to link to an already built dashboard or explore within looker. The external page could be a link to the brands home page or simply google search with the clicked on value injected into the link as the search term.

Say we wanted to link out to Google when we click on a brand name on our dashboard and have the link automatically search google for the brand we clicked on. We can use liquid to inject the brand name into the Google URL by using {{ value }} object. This references the value we clicked on and pops it into the url.

url: "http://www.google.com/search?q={{ value | encode_uri }}"

We also want to give the option to link to a dashboard that has already been built and we want to pass the filters from the current dashboard to the this dashboard to continue our analysis. we want to dynamically filter the dashboard based off values from the current dashboard.

We can use liquid to grab the value of the dimension and pass it into the filter of the second dashboard. We can do this by using value object and setting the value of the filter, Brand={{ value }}
We can also grab other values from the query and pass them into the dashboard also as other filters. This is done by using the following syntax for filters FILTER_NAME= {{ _filters['view.field_name] }} and {{ view_name.field_name }} for fields.
url: "dashboards/24?

Brand={{ value }}

&Category={{ _filters['products.category'] }}

&Department={{ products.department | url_encode }}"

Links can also take users to a pre-configured Explore with dimensions, measures, and filters already present on the page. The selected value is often input as a filter into the Explore, providing a customised drill-through experience.

We can specify a list of fields we want present in the explore by defining each filed in the url, When linking to an explore we use a slightly different syntax for defining the filters we want to pass values too. A quick way to get the correct url is to create an explore with filters applied and grab the expanded url, Replace the filters with the syntax for filters f[view.field]={{ _filters[view.field] }}

url: "explore/project/products?

fields=products.id,products.brand

,products.item_name,products.retail_price

,products.department,products.category

&f[products.brand]={{ products.brand | url_encode }}"

What does it look like when we put them all together.

How it looks in the LookML

dimension: brand {
sql: ${TABLE}.brand ;;
link: {
label: "Search Google for {{ value }}"
url: "http://www.google.com/search?q={{ value | encode_uri }}"
icon_url: "http://www.google.com/s2/favicons?domain=www.{{ value | encode_uri}}.com"
}
link: {
label: "{{ value }} Analytics Dashboard"
url: "https://instance.looker.com/dashboards/24?
Brand={{ value }}
&Category={{ _filters['products.category'] }}
&Department={{ products.department | url_encode }}"
icon_url: "http://www.looker.com/favicon.ico"
}
link: {
label: "Explore {{ value }}"
url: "https://instance.looker.com/explore/project/products?
fields=products.id,products.brand
,products.item_name,products.retail_price
,products.department,products.category
&f[products.brand]={{ products.brand | url_encode }}"
icon_url: "https://looker.com/favicon.ico"
}
}

Advanced Linking with Liquid

For even more advanced use cases that require different links in different Explores, use liquid tags to incorporate conditions into Links:

dimension: state {

sql: ${TABLE}.state ;;

html: {% if _explore._name == "order_items" %}

<ahref= "/explore/project/order_items?fields=order_items.detail*&f[users.state]= {{ value }}">{{ value }}</a>

{% else %}

<a href="/explore/project/users?fields=users.detail*&f[users.state]=

{{ value }}">{{ value }}</a>

{% endif %} ;;

Check out some of the following links if you want to read more on linking and drilling
Help centre article here
More Powerful drilling here

Date Formatting with Liquid

Another popular use case for liquid in Looker is for customers to use liquid and html to format the data that they display in Looker. Sometimes dates are in a different format and customers will use liquid to change the format. They can dynamically change dates and currencies with liquid and user attributes.

We can make use of the HTML parameter and pass a user attribute into some liquid conditions and format dates accordingly. Because we are using it on the html parameter, we are not changing the original value, only the way it is rendered to the user.

For example if we want to build a dashboard the is going to be displayed to both American & European users we might have the difficulty of displaying the dates in two different formats based of what region a user is in. With liquid we can do this pretty easily.

First we make sure every user has a user attribute for the region they are in, Then we can create a time dimension that checks the uses region and outputs a formatted date accordingly.
dimension: date_formatted {

label: "Date_formatted"

sql: ${created_date} ;;

html:

{% if _user_attributes['region'] == 'EU' %}

{{ rendered_value | date: "%m/%d/%y" }}

{% endif %}

{% if _user_attributes['region'] == 'USA' %}

{{ rendered_value | date: "%d/%m/%y" }}

{% endif %};;

}

We pass the value of the dimension through the liquid date filter which converts a timestamp into another date format. The format for this syntax is the same as strftime
 

With strftime formats, we can change the format of anything, as long as it’s a valid date format. In the example below, we are formatting both, day, week and month differently, and we add the created group_label to simulate a date dimension group.

dimension: date_formatted {

group_label: "Formatted" label: "Date"

sql: ${created_date} ;;

html: {{ rendered_value | date: "%b %d, %y" }};;

}

dimension: week_formatted {

group_label: "Formatted" label: "Week"

sql: ${created_week} ;;

html: {{ rendered_value | date: "Week %U (%b %d)" }};;

}

dimension: month_formatted {

group_label: "Formatted" label: "Month"

sql: ${created_month} ;;

html: {{ rendered_value | append: "-01" | date: "%B %Y" }};;

}

dimension: more_formatted {

group_label: "Formatted" label: "Full"

sql: ${created_date} ;;

html: {{ rendered_value | date: "%A, %B, %e, %Y" }};;

}

Templated Filters and Parameters

Looker give users the ability to manipulate queries by allowing them to add filters to the front end. These filter can filter results based off messages and dimensions in the query. This feature meets many use cases, it can’t enable every analytical need. Templated filters and Liquid parameters vastly expand the possible use cases you can support.

Adjusting part of a derived table, adjusting which database table gets queried, or creating multipurpose dimensions and filters are just some of the features you can enable with templated filters and Liquid parameters.

We want to give as much flexibility to our users on the front end as possible without having to custom code for each use case. So What does “dynamic” really mean? We say a lot of this is dynamic, meaning when the front end user makes a change, and this rewrites the back-end SQL logic to align with that change.

Templated filters and Liquid parameters make use of the Liquid templating language to insert user input into SQL queries. First, you use a LookML parameter to create a field for users to interact with. Next, you use a Liquid variable to inject the user input into SQL queries
 

Derived tables
 

Derived tables are probably the most common use case for templated filters, because they allow us to restrict tables to certain criteria prior to building them.
In Looker you can write subqueries, or derived tables, essentially passing a templated filter to this query to operate on a subset of data.

Templated filters are created using a filter: LookML Object. As a parameter, it can be type string, number, date. Suggest Explore: the Explore that will be queried in order to pull a list of suggested filter values
Suggest Dimension: the dimension that should be used within the suggest Explore for providing a list of suggested filter values

filter: state_filter {

type: string

suggest_dimension: users.state

suggest_explore: users

Templated filters are referenced inside of a liquid block using the syntax {% condition filter_name %}. This prepares the liquid to apply a templated filter. We then give it the name of the field we want to apply that filter to and finish the liquid block with an endcondition tag.

Say we take an Orders table that shows all the lifetime orders for a company, We want to allow users to only select a subset of this data. We decide users can chose to input a date range and an order status. In order to make these fields dynamic we create a templated filter for the date and a parameter filter for the status so that they can interact with these fields.

How does this look in the LookML

filter: date_filter {
type: date
}

parameter: status_parameter {

type: string

allowed_value: { value: "pending" }

allowed_value: { value: "cancelled" }

allowed_value: { value: "complete" }

}

How does it look in the front end

We define a parameter field with a set of allowed_value sub-parameters. The parameter is surfaced as a filter-only field in the UI and the user can choose only one of the allowed values:

Now we need to make the derived table SQL take these inputs from the user, We can do this using the following liquid syntax in the WHERE section of the derived table. This will dynamically change the SQL generated based off the values selected form the front end.

How it looks in the SQL of the derived table
WHERE

{% condition status_parameter %} status {% endcondition %}

and

{% condition date_filter %} created_date {% endcondition %};;

How it looks in Lookers generated SQL

WHERE

(status = 'cancelled')

and

((( created_date ) >= (CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', TIMESTAMP '2017-02-01')) AND ( created_date ) < (CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', TIMESTAMP '2017-03-31'))))

)

Dynamic Filtered Measure

We want to create a filtered measure where users can change the traffic source of the order they want to count. Users on the marketing team need to analyse the share of orders coming from each source.

What if I have hundreds of values that a user could filter for? I don’t want to write hundreds of case whens. What if a new value gets added to my data table? I don’t want to have to update my logic then either. we need something more dynamic.

We can Create a single measure that allows a user to choose any available Traffic Source and see a count of users for that traffic source (to then be compared to the overall count of users to calculate a percentage)
filter: incoming_traffic_source {

type: string

suggest_dimension: derived_orders.traffic_source

suggest_explore: derived_orders

}

We can create a yesno dimension that takes the inputted value from the front end and checks it against the value in our dimension.

dimension: hidden_traffic_source_filter {

hidden: yes
type: yesno

sql: {% condition incoming_traffic_source %} ${traffic_source} {% endcondition %} ;;

}

We can then filter the measure on yes. Allowing our users to filter down the count on any traffic source that is present in our database.

measure: changeable_count_measure {
type: count_distinct
sql: ${id} ;;
filters: {
field: hidden_traffic_source_filter
value: "Yes"
}
}

Input values for templated filters can come from Filter fields Regular dimension and measure fields. In derived tables, the templated filter will act upon a column or set of columns from the underlying database. When regular fields are used, filters will be applied in two places: The templated filter & The WHERE clause of the outer query

{% condition filter_name %} sql_or_lookml_reference {% endcondition %}

If the user entered “2012-01-01 to 2014-12-31” into the date filter, Looker would turn these tags into: orders.date BETWEEN '2012-01-01' AND '2014-12-31'. In other words, Looker understands the user input and generates the appropriate logical expression.

Some more advanced filtering use cases.

• Dynamically Selecting Fields using Parameters
• Timeframe vs. Timeframe Analysis Using Templated Filters
• Dynamically Query Tables Using Parameters
• Dynamic Timeframes for Dimension Groups