Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

- - Knowledge base
Product Announcements
Events
Groups

Community
The Kitchen Table

The Kitchen Table

Explore the product knowledge we've built up together

2115 Topics

Recently active

Most replies Most views Newest first

samLooker Staff

published in Technical Tips & Tricks

How can I stop or change the order of my PDT builds?Knowledge Drop

Last tested: Feb 2021ProblemI have several PDTs building, and I want to either: Pause a PDT from building Control the order of my PDT builds (maybe I want a certain one to always build first) ExplanationThe order of PDT builds is arbitrary except when dependencies are involved. So there isn't any UI for pausing a PDT or changing the order of PDT builds (here's a feature request to +1 if you'd like to see this feature).WorkaroundsTo pause trigger checks for an entire connection:You can use the PDT Maintenance Schedule to control when Looker sends trigger queries to that database.It's a cron string, which means you can get a full pause by setting the date to something in the future. For example, if today is the 2nd and you want to pause all trigger checks you could set the cron task to 0 * 31 12 * for "every hour on the 31st of December".To pause trigger checks for one PDT:You can comment out sql_trigger_value or persist_for for a given PDT and push it to production to stop it from bui

5201

6 months ago

samLooker Staff

published in Technical Tips & Tricks

How can I merge user attribute values from multiple groups?Knowledge Drop

Last tested: March 2021 Background: Groups are given user attribute values. For example, group_USA might be given the value for the “country” user attribute as “USA”. With an access filter, users in that group will get WHERE country = “USA” for their queries.The issue: If a user belongs to two groups, like group_USA and group_UK, each with different “country” user attribute values, this user will not inherit both user attribute values. Other types of permissions are additive with groups, but user attributes are not: one will “win out”.To solve the scenario where someone would need access to both countries, you might think you need to create a user attribute for each combination of user attribute values. While that would work, managing all the combinations might get unwieldy. The following pattern is an alternative.1. Create a user attribute for each possible individual value. Ex: a user attribute for every count2. Create a group for each user attribute.3. Assign groups to user attribut

5784

6 months ago

VincentLooker Staff

published in Technical Tips & Tricks

jaxLooker Staff

published in Technical Tips & Tricks

Using parameters to find the top N items versus the rest of the population

Looker will not be updating this content, nor guarantees that everything is up-to-date. The Looker parameter field lets you inject a value directly into the SQL.Various Looker pages describe how to use this feature:The Templated filters and parameters documentation page, which discusses creating the field and applying the user input The parameter reference page for this field type and its allowed_value subparameter The Field parameters page, which includes the parameter field type and shows which LookML parameters can be subparameters of the parameter field The Liquid variable reference documentation page, which mentions the {% parameter %} Liquid variable The Dimension, filter, and parameter types documentation page, which indicates the LookML type parameter values that can be used with parameter fieldsIn this article, we discuss how to use parameter to let users find the top N items versus the rest of the population in Explores. The problemUsers often want to find out the top n perfo

15520

6 months ago

jaxLooker Staff

published in Technical Tips & Tricks

Dynamic schemas with the same data format using parameter

Looker will not be updating this content, nor guarantees that everything is up-to-date. The Looker parameter field lets you inject a value directly into the SQL.Various Looker pages describe how to use this feature:The Templated filters and parameters documentation page, which discusses creating the field and applying the user input The parameter reference page for this field type and its allowed_value subparameter The Field parameters page, which includes the parameter field type and shows which LookML parameters can be subparameters of the parameter field The Liquid variable reference documentation page, which mentions the {% parameter %} Liquid variable The Dimension, filter, and parameter types documentation page, which indicates the LookML type parameter values that can be used with parameter fieldsIn this article, we discuss how to use parameter to dynamically select from multiple schemas with the same data formatThe problemSome schemas that are identical in structure. This is of

5820

6 months ago

samLooker Staff

published in Technical Tips & Tricks

Why can't I access chat?Knowledge Drop

Last tested: April 2021 If you don't see the chat option in the Help Menu:Permissions: Anyone with the develop permission can come on chat. This means that admins have this capability too. Check if the user is missing the develop permission.License type: If you have a partner license type you will not have chat access starting April 1st 2021. As for trials, trials which were initiated after Jan 5th 2021 will not have chat feature toggled on. However, the trials that were initiated before that are being turned off in a phased manner.Closed ports: If your Looker instance is customer-hosted, then your network administrator needs to open several ports on your server to connect to the chat client. If you see the chat option in the Help Menu, but clicking on it doesn't bring up the chat widget:Browser extensions: If you do not see the chat option in the help menu, but clicking it does not bring up the chat widget, check in incognito mode. Often times we've seen extensions such as Ghostery ca

11602

6 months ago

paolaLooker Staff

published in Technical Tips & Tricks

Can you format dates in table calculations?Knowledge Drop

Last Tested: Jan 21, 2019 If you have a date field with values such as "2021-01-01", you can use excel formatting to display the date as January 1, 2021. You can also use this approach to only display the month name, such as "January", etc.Excel uses the date January 1, 1900 as serial number 1. This means that January 1, 2008 is serial number 39448. In a table calculation, you can use the diff_days(start_date, end_date) function to return a number between your date field and January 1, 2008. Once you have that number, you can add 39448 to it.Given the above, this is the exact formula which can be used to allow the date to be formatted using the custom format syntax:diff_days(date(2008,01,01), ${orders.created_date}) + 39448 Once we have this defined, we can use the table calculation "Custom..." formatting option to display our dates as January 1, 2008.Outcome in the Explore UI:The available formatting options can be found here. This content is subject to limited support.

8371

6 months ago

samLooker Staff

published in Technical Tips & Tricks

30 Day Windows: Periods Starting from Today

Looker will not be updating this content, nor guarantees that everything is up-to-date. The ProblemWhen we want to look at our data in terms of 30-day periods, we can use Looker's built in month timeframe for dimension_group fields. However, this can pose a problem, as not all months are the same length. Also, in cases where the current day is in the middle of a month, the data for that "period" is incomplete. The goal in this case is to get a period for every 30 days (i.e., past 0-30 days = 1, past 30-60 days = 2, 60-90 days = 3, etc.), starting from today and moving backwards, by which to group our data. The SolutionIn Looker, we can achieve this pattern by placing a SQL function in the SQL: parameter of a dimension. For example, let's say that we have a date dimension_group called created:dimension_group: created { type: time timeframes: [time, date, month, raw] sql: ${TABLE}.date ;; }Then, all we need to do is create a new dimension of type: number to capture the number of days

7721

6 months ago

samLooker Staff

published in Technical Tips & Tricks

How do I render a local image in Looker?Knowledge Drop

Last tested: March 2021 Looker expects a public URL for your image. So make sure that wherever you store it is publicly accessible. Then, you can call it in LookML like so:view: view_name { dimension: field_name { html: <img src=”http://www.acme.com/product_images/my_image.jpg” /> ;; }}This help center article goes more in depth on the modeling.It's not possible to use a local image on your computer, or on the Looker server. It is also not possible to use non-public URLs, as Looker will have no way to authenticate. This content is subject to limited support.

13106

6 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Troubleshooting Private Embedding + SAMLKnowledge Drop

Last tested: Oct 7, 2020 There are a number of errors and settings to be aware of when using SAML authentication in an iframe for private embedding. You can test things out using a simple online HTML editor, using the following iframe code:<iframe src="https://instance.looker.com/embed/dashboards/[id]" height="500" width="500"></iframe> 401: You are not authenticated to view this pageIf you are not already logged into Looker, you will get this page by default when you attempt to access the embed URL through the iframe instead of getting the Authenticate button. To avoid this error, simply add allow_login_screen=true to the URL:<iframe src="https://instance.looker.com/embed/dashboards/[id]?allow_login_screen=true" height="500" width="500"></iframe>Note that even if you are using the sso_bypass_login_page license feature to skip the login page, you will still need to add allow_login_screen=true to avoid an error. You have to allow the login screen before you can b

8681

6 months ago

samLooker Staff

published in Technical Tips & Tricks

How to Add Images to Visualizations

Looker will not be updating this content, nor guarantees that everything is up-to-date. The ProblemI have pictures that I want to show in my Looker visualization. In a dataset of companies, I could use this to replace a company name with an image in a visualization: The SolutionWe can use the html LookML parameter to have Looker render HTML instead of just showing a text value. In this case, we can use HTML's img tag to have a field just render a picture:dimension: looker_image { type: string sql: ${TABLE}.homepage_url;; html: <img src="https://logo-core.clearbit.com/looker.com" /> ;;}The text for each of the rows has now been replaced by the image defined in the img tag of the HTML. Dynamic ImagesThe next step is to make my images change based on the value of the row. We can input the value of the row into the html parameter using the Liquid variable {{value}}. We can add this to the image URL in a slightly modified version of the dimension above: dimension: brand_images { t

30403

6 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

REPLACE Characters in string filtersKnowledge Drop

Last tested: Jul 30, 2019 If you see odd results in a drill's filter string, it may be that the strings content has a character that Looker is trying to escape. Characters like - Looker is trying to escape, so we need to do something to avoid that.To avoid this, you'll want to replace the character(s) that Looker is trying to escape using something like the REPLACE function. This way both the field and the filter results will have the proper character string for filtering. dimension: namesql: REPLACE(${TABLE}.name_field, "-", " ") This content is subject to limited support.

26211

7 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Add a total's column to a stacked bar chartKnowledge Drop

Last tested: Oct 14, 2020 Using Table Calculations, you can add a total column to a stacked bar chart. (Note: If your bar chart is not stacked, you can add a total column by simply checking the row totals box and pivoting on your dimension)METHOD 1: TOTAL COLUMN WITH BREAKDOWNStep 1: Configure your explore section so that it has one extra row than the number that you need to display. For example, in this graph we only want to display March - July, but we'll want to add 1 extra row so that we can display a total column at the end.Step 2: Create two table calculations. The first will create a column that displays the total of the series if it is the max row in the data set. The second will create a column that displays the word "Total" if it is the max row in the data set. Step 3: Hide your original dimension and measure from the visualization so that only the table calculations and the pivoted columns remain in the visualization.METHOD 2: TOTAL COLUMN WITHOUT BREAKDOWN (PRESERVES DR

19072

7 months ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

Methods for Period Over Period (PoP) Analysis in Looker - Method 6: Any Two Arbitrary Periods

Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 6: Any Two Arbitrary Periods highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw, time,

15641

7 months ago

maxcorbinLooker Staff

published in Technical Tips & Tricks

How to Count Only Weekdays Between Two Dates

Looker will not be updating this content, nor guarantees that everything is up-to-date. If you'd like to count the days between two dates, you could use the SQL DATEDIFF function, which simply counts the days between two points in time:Redshift:DATEDIFF('day', start, stop) AS daysPostgres:DATEPART('day', start - stop) AS daysMySQL:DATEDIFF(stop, start) AS daysSnowflake:DATEDIFF('day', start, stop) AS daysHowever, the functions above will count all the days between the two specified dates. What if you want to exclude weekend days (Saturday and Sunday)?One way of achieving this is to create a derived table with the necessary logic. But, there is a more straightforward way, using a single short query, which you can define in the sql parameter of a dimension or measure in LookML.Note:You will want to make sure your database will return a day of week index of 0 for Sunday and 6 for Saturday; this is important, or the calculations will be one or two days off. You will also want to make sure

56722

7 months ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

Methods for Period Over Period (PoP) Analysis in Looker - Method 8: Arbitrary Period and Many Previous Periods

Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 8: Arbitrary Period and Many Previous Periods highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post. Period-over-Period Method 8: Arbitrary Period and Many Previous Periods - Compare Any Period to Any Other Period This method allows users to choose the current period, the grouping for that period, which period to compare (pivot), and how many periods to compare. This is the most complex PoP method, but it is also the most flexible.Below is an example with order items in the current month-to-date, broken down by day and compared to last month:Below is an example with an arbitrary date range, broken down by day and compared to the same date range for the previous three years:Advantages and DisadvantagesAdvantages:The date range can be broken to a more granular level (e.g., months, days, weeks). You can be

12991

7 months ago

maxcorbinLooker Staff

published in Technical Tips & Tricks

How to do Period-over-Period Analysis

Looker will not be updating this content, nor guarantees that everything is up-to-date. See the Methods for Period Over Period (PoP) Analysis in Looker Community post for more examples of period-over-period analyses in Looker.The BasicsPeriod-over-period analysis is a pattern of analysis that measures something in the present and compares it to the same measurement in a comparable period of time in the past. The goal is to adjust the slice of the past you are looking at, so the same amount of time has passed in the two periods you are comparing. How-To: Year-over-Year (YOY)You can do period-over-period analysis over any defined time period. Our example is going to use year-over-year, because this is a very common use case. This example can be adjusted for comparing other time frames, such as month-over-month.In year-over-year analysis, we're looking to compare the current year to date with last year up to the same date. But if we simply filter a query for the last two years, we are goi

76241

7 months ago

veronica.phillipsCommunity Admin

published in Technical Tips & Tricks

Masking Sensitive Fields for All Users

Looker will not be updating this content, nor guarantees that everything is up-to-date. There are several reasons you may want to mask the data in a field from some or all users. Maybe you want to restrict the ability for certain users to view the data for a specific field. Or maybe you want all users to be able to see the field but would like to partially obfuscate the data in that field.In the latter case, say you want to mask credit card information* so that some characters in the card number are replaced with asterisks. You can do this right in your Looker model by concatenating a combination of LEFT, RPAD, LEN, and RIGHT SQL functions in your SQL parameter.For example, you can create a new dimension masked_credit_card_number for a 16-digit field credit_card_number (this one uses MYSQL syntax):dimension: masked_credit_card_number type: string sql: CONCAT(LEFT(${TABLE}.credit_card_number, 4), RPAD('X', LEN(${TABLE}.credit_card_number) - 8, 'X'), RIGHT(${TABLE}.credit_card

3390

7 months ago

lindseyLooker Staff

published in Technical Tips & Tricks

Comparing a Previous Week to that Same Week Last Year

Looker will not be updating this content, nor guarantees that everything is up-to-date. It is common to compare things like the sales from a specific week of this year with the sales from the same week in previous years. Let's say you want to compare last week to the same week last year. There are two ways to accomplish this:Weeks Starting on MondayIf you are using Looker's default weeks starting on Monday, you can do this easily by creating a matches (advanced) filter on a date field (in the example below we use a week timeframe to show a count by week) with the following filter expression:1 week ago for 1 week, 53 weeks ago for 1 weekThis filter will output something like this:Note: We will need to turn off the dimension fill feature on the date field in the Explore to compare only the time periods that were filtered for. Otherwise, we may see NULLs in the result set for the count for filled-in dates.Weeks Not Starting on MondayIf you want to analyze weeks that start on a day other

17880

7 months ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

Methods for Period Over Period (PoP) Analysis in Looker - Method 7: Arbitrary Period and Directly Previous Period

Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 7: Arbitrary Period and Directly Previous Period highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw,

13100

7 months ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

Methods for Period Over Period (PoP) Analysis in Looker - Method 5: Current Period and Any Arbitrary Period

Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 5: Current Period and Any Arbitrary Period highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw,

8120

7 months ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

Methods for Period Over Period (PoP) Analysis in Looker - Method 4: Current Period and Many Previous Periods

Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 4: Current Period and Many Previous Periods highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw,

8710

7 months ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

Methods for Period Over Period (PoP) Analysis in Looker - Method 3: Current Period and Previous Period

Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 3: Current Period and Previous Period highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects. view: order_items { sql_table_name: public.order_items ;; dimension: id { primary_key: yes hidden: yes type: number sql: ${TABLE}.id ;; } dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw, time,

40840

7 months ago

Page 4 / 85

Badges

sbrylowskihas earned the badge Friendly Face
carriehas earned the badge Helpful
carriehas earned the badge Community Star
carriehas earned the badge Friendly Face
Dawidhas earned the badge Community Star

Show all badges

Sign up

Already have an account? Login

Social Login

Username *

E-mail address *

Company

(Private)

Only you and moderators can see this information

Password *

I accept the Terms of Service.

If you’re an EEA-based consumer of digital content, services, or goods, and you’ve agreed to our Terms of Service, then EEA consumer laws provide you with a legal guarantee. Under this guarantee, we’re liable for any lack of conformity that you discover for the one-time supply of digital content or services at any time during the “continuous” supply of digital content or services.
Your national laws may provide an even longer guarantee. Your rights under these legal guarantees aren’t limited by any other commercial guarantees that we provide. If you want to make a guarantee claim or if you would like to delete your account data, please contact us lookerconnect-help@google.com.

loginBox.register.email_repeat

Login to the community

No account yet? Create an account

Social Login

Username or Email

Password

Remember me

Forgot password?

Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.

Username or e-mail

Back to overview

Scanning file for viruses.

Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.

This file cannot be downloaded

Sorry, our virus scanner detected that this file isn't safe to download.

The Kitchen Table

How can I stop or change the order of my PDT builds?Knowledge Drop

How can I merge user attribute values from multiple groups?Knowledge Drop

Can I use `hidden` on a join?Knowledge Drop

What does "async socket check failed" mean in the logs?Knowledge Drop

"Imported content is empty" in Google Sheet or showing wrong dataKnowledge Drop

Why can't I access chat?Knowledge Drop

Can you format dates in table calculations?Knowledge Drop

How do I render a local image in Looker?Knowledge Drop

Troubleshooting Private Embedding + SAMLKnowledge Drop

REPLACE Characters in string filtersKnowledge Drop

Add a total's column to a stacked bar chartKnowledge Drop

Badges

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded