YesNo — The Not-So-Secret Solution to a More User-Friendly Looker Experience

Photo by John Schnobrich on Unsplash

One of Looker’s biggest selling points is how self-service and straightforward it is for business users. Offering readily available and accurate data is great, but sometimes it can be difficult to inspire end users to take the plunge into real analysis.

As a LookML developer, I am often trying to figure out how I can continue to build experiences within the platform that make it easier for my business users to actually get the information they need to make informed decisions quicker. I’ve found that using ‘yesno’ (boolean) fields can help to achieve this by providing versatility and allowing for more customization that can guide them towards what they’re actually looking for.

Below, I will go through some ways you can better utilize yesno fields. I will use the dataset from a retail clothing website in my examples.

Comparing Timestamps

If you have a condition that needs to be met regarding the comparison between 2 timestamps, a yesno dimension may give you a little more flexibility than a custom case when statement.

Use case: There is sometimes miscommunication between the sales team and warehouse team, and orders may get shipped after they were cancelled. We want to know the total value of orders where that happens. First, we’d make the yesno dimension.

dimension: shipped_after_cancelled {
type: yesno
sql: ${shipping_date} >= ${cancel_date} ;;
}

You could stop here and do some cohort analysis with this dimension, or you can continue by making a measure of the revenue filtered when the ship date is on or after the cancel date. This way you can understand how much revenue was lost by this type of error.

measure: revenue_shipped_after_cancelled {
type: sum
sql: ${revenue} ;;
filters: [shipped_after_cancelled: “Yes”]
}

YesNo — a Dimension AND a Measure

Did you know that you can use type: yesno as a dimension or a measure? Yesno makes sense as a type for a dimension, but Looker has also allowed yesno to be a measure in the case you want to create a boolean field from an aggregation.

Use case: We already have a measure to sum order value:

measure: order_value {
type: sum
sql: ${sale_price} ;;
value_format_name: usd
}

Now, we want to add a yesno flag to indicate whether it was a “large order”. We wouldn’t be able to create a dimension to do this because order_value is an aggregation. Looker requires you to make this a measure instead. To ensure a consistent definition of “large order” and to allow for the value to be referenced in various fields, we can define it as a constant in the project manifest.

constant: large_order_threshold {
value: “1000”
}

Now, we can create a yesno measure using this constant in the sql definition.

measure: is_large_order {
type: yesno
sql: ${order_value} > @{large_order_threshold};;
}

Complex Conditional Formatting

We want to add conditional formatting to a dimension, which you can’t do in the edit pane of the visualization for a table. This is possible using the html parameter, but when the condition we want to satisfy is a little bit more complicated, we can’t rely on liquid syntax alone.

Use case: We want to add conditional formatting to our Product Name so that if it’s status = Clearance and it’s not in the Product Name, it’ll be red. We can’t use a like operator in the liquid tag syntax, so we will use a yesno field first.

dimension: refurbished_product_name_check {
hidden: yes
type: yesno
sql: ${product_type} = ‘Clearance’ AND ${product_name} not ilike ‘%clearance%’;;
}

dimension: product_name {
type: string
sql: ${TABLE}.product_name ;;
html:
{% if refurbished_product_name_check._value == ‘Yes’ %}
<div style=”color: black; background-color: red; font-size:100%”>{{ value }}</div>
{% else %}
{{ value }}
{% endif %}
;;
}

DRY (Don’t Repeat Yourself) Filters

Requests for analysis can outpace the available data in the existing structure of your database.

In situations where desired cohorts or categories are required, but not uniquely identified in the underlying data, we can use LookML yesno dimensions. End users can use these yesno dimensions to easily filter or create cohorts. This can reduce the need to repeatedly add filters or write out complex custom filters.

Use case: During the winter and fall, a set of product categories are often run in promotion together because they are considered to be “warm clothing”. Rather than calling out each category every time, we can create a yesno dimension in the LookML.

dimension: warm_clothing {
type: yesno
sql: ${product.category} in (‘Blazers & Jackets’,‘Fashion Hoodies & Sweatshirts’,‘Sweaters’,‘Outerwear & Coats’) ;;
}

You can also use a yesno dimension to replace business logic that we might otherwise put into a custom filter. This would allow end users to easily filter without needing to write expressions in LookML syntax.

Use case: Last year, we had a Black Friday sale on our website that lasted 1 week. Additionally, we had promotions on Amazon for Cyber Monday. We could easily add a custom filter to an explore related to this subset of promotions, but if we add it to the LookML, it will allow users to more easily create Black Friday reporting.

dimension: black_friday_2020 {
type: yesno
sql: (${sales_channel} = ‘Website’ AND ${sales_date} >= ‘2020–11–26’ AND ${sales_date} <= ‘2020–12–2’) OR (${sales_channel} = ‘Amazon’ AND ${sales_date} = ‘2020–11–30’) ;;
}

Adding this to your LookML will ensure the definition is consistent every time it’s used, which will minimize the risk of inaccuracy.

Default dashboard filter settings based on user attributes

For users that may not be used to looking at data, we may want to make it even easier for them by automatically filtering the dashboard for their specific settings, however we don’t necessarily want to limit their data access (so an access_filter would not work). Looker does give us the option to filter a dashboard to match a user attribute, however if the user wants to look at the dashboard based on other filter settings, it may not be as intuitive to change it correctly.

Another option would be to create a yesno dimension to check whether a user attribute matches the filtered field. We can default this to Yes, then link the filters so that by default, the user only sees the filter field that matches their user attribute. They would simply uncheck the Yes to filter on whatever values they want to.

Use case: We want sales associates’ default settings when they visit a dashboard to be for their region.

1. Create the user attribute. To ensure this functions correctly, we will use the “String Filter (Advanced)” data type. Here is the syntax for the following types of inputs:

Single Value: ‘California’
Multiple Value: ‘California’,’Oregon’,’Washington’
Any Value: % (no quotations)

2. Create a yesno dimension that will check if the region matches the one in the user attribute. Since the ‘%’ wildcard will not work with the IN operator in SQL, we need an IF statement with liquid syntax to account for it.

dimension: region_settings {
type: yesno
sql: {% if _user_attributes[‘sales_region’] == ‘%’ %}
1=1
{% else %}
${customer_region} in ( {{ _user_attributes[‘sales_region’] }} )
{% endif %} ;;
}

3. Add a filter for the customer_region to the dashboard you want the ability to update.

4. Add another filter to look at the region_settings. Here are the settings you must have:

• Values = “Yes” only; This is important because if you allow “No”, then it will not let you choose the user’s region. “Yes” filters to the user’s settings and unchecking it allows any value.
• Configure Default Value so “Yes” is checked.
• Under Additional Options, check “Select filters to update when this filter changes” and add customer_region. This will make sure the region will automatically get filtered if the Yes is checked.

5. Here are my recommendations for the other settings to keep it clean and easy to read:

• Control = Checkboxes
• Display = Inline

Closing Remarks

While creating yesno fields in LookML has the potential to add slightly more maintenance to your Looker model, getting your business users the information they need quicker will encourage them to seek it more often.

Not only can empowering your users with yesno fields provide them information more efficiently, but it will also deliver better consistency. When users trust the insights that are coming from Looker, it will encourage your company to be more data-driven and allow everyone (including you!) more time to work on more meaningful things.

Can you think of other ways to use yesno fields in Looker? If so, leave a comment below because I’d love to hear of more!

I’d like to acknowledge my colleague David Brinegar for collaborating with me to come up with some of these solutions. I’d also like to thank Sarah Wilson and Spencer Taylor for helping me to edit this post.