Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

Knowledge base
Community ResourcesUsing the Community3 Account Info & Registration4 Community Feedback1
Community
WelcomeGetting started154 News & announcements128 Who's hiring443 Release notes (archive)63 Lesson Comments15 Challenge10
Looker developmentLookML1897 SQL327
Exploring & curating dataExplores582 Dashboards & looks1629
Application developmentEmbedding Looker / Powered by Looker163 Open source projects56 Looker API388
AdministrationUser access & management77 General Looker administration467
Data science & analyticsGot a question?87 Share your best practices16
The Kitchen TableTechnical Tips & Tricks1741 Common Account Questions14 Blog Archives347 DCL Mic Drops5 Video Tutorials 🎥5
コミュニティフォーラム (Japanese)ニュースと告知52 ヘルプとサポート14 コラム69

Ask question / Create topic
Login

Community
The Kitchen Table

The Kitchen Table

Explore the product knowledge we've built up together

2112 Topics

Recently active

Most replies Most views Newest first

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.

8356

1 month ago

Department of Customer LoveModerator

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

6181

1 month ago

samLooker 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,

4441

1 month 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

15162

1 month 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

3951

1 month 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

21781

1 month ago

veronica.phillipsLooker Staff

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

1190

1 month ago

Department of Customer LoveModerator

published in Technical Tips & Tricks

Override Look Filters in the URL

Looker will not be updating this content, nor guarantees that everything is up-to-date. We often want to use a saved look as a sort of template and then dynamically override specific filter values, such as when we embed Looker into another application.This post shows you how to do this.Overriding Filters The following example shows a Look where the default value for Orders Created At is saved as on the day 2017-11-01. We see this URL (replacing <instance_name.looker.com> with the URL to your company's instance):https://<instance_name.looker.com>/looks/:id: In this case, toggle=fil appears in the URL, because we have expanded the filters box to show the value. We can override the filter value by visiting a URL and changing it to this:https://<instance_name.looker.com>/looks/:id?f[orders.created_date]=7+days Looker automatically rewrites the URL into a unique query ID (qid) parameter representing this ad hoc query, just like on an Explore page. This is the format o

2600

1 month 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

4830

1 month 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,

3260

1 month 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,

1770

1 month 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,

2520

1 month 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,

9540

1 month ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

Methods for Period Over Period (PoP) Analysis in Looker - Method 2: Any Two Native Timeframes (with Liquid)

Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 2: Any Two Native Timeframes (with Liquid) 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,

3840

1 month ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

Methods for Period Over Period (PoP) Analysis in Looker - Method 1: Any Two Native Timeframes

Looker will not be updating this content, nor guarantees that everything is up-to-date. This post describes the period over period (pop) Method 1: Any Two Native Timeframes 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,

5760

1 month ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

Methods for Period Over Period (PoP) Analysis in Looker

Looker will not be updating this content, nor guarantees that everything is up-to-date. Looker can implement period-over-period (PoP) analysis with a number of methods that vary in functionality and modeling difficulty. These factors need to be balanced depending on requirements and expertise. This article addresses eight methods for performing PoP analysis, outlined in the chart below. Use the chart to determine the appropriate method for your use case, and select the name of the desired PoP analysis method in the Method implementations section below: Method implementationsSelect the name of the desired PoP analysis method below to be directed to the implementation instructions. A majority of the examples use an order_items table from an e-commerce dataset. The example LookML view below is the basis of these examples: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

32080

1 month ago

samLooker Staff

published in Technical Tips & Tricks

Computing an Average Across Pivot Columns

Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 21.12, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when the Custom Fields Labs feature is enabled, and you have permission to use them. The ProblemWhen we have a result set like the one below, we might want to compute the average for each state across the pivot columns. In Looker table calculations, this is achieved with the mean() function.To compute our average, we need to find the sum of the values across each row, and then divide that sum by the number of columns. We can do this by creating a table calculation with the example below. An Elegant SolutionWe'll take advantage of the pivot_row() function to help us dynamically sum the row values across pivot columns and get the right column number.The pivot_row() function takes the values from all columns across a r

13830

1 month ago

samLooker Staff

published in Technical Tips & Tricks

How to Compute the Number of Days in a Month

Looker will not be updating this content, nor guarantees that everything is up-to-date. There are several situations where you might want to get the number of days in a given month.You might try to do this by hard-coding the number of days for each month into a new dimension with a 12-case CASE WHEN, but then you'd also need to handle leap years, where the number of days in February changes.A better solution is to write a table calculation that can dynamically account for leap years. The Table CalculationGiven a date dimension ${date} (where you would replace this placeholder with the name of your actual date field), this calculation returns the number of days in the month of ${date}:extract_days(add_days(-1, date(extract_years(add_months(1, ${date})), extract_months(add_months(1, ${date})), 1)))This calculation starts with the first day of the next month. The calculation then moves back one day, checks the day of the month, and returns the number of that day. The In-Depth ExplanationF

4510

1 month ago

samLooker Staff

published in Technical Tips & Tricks

Custom Filters: Exactly 1 Month Ago

Looker will not be updating this content, nor guarantees that everything is up-to-date. The Problem When analyzing data, you might want to see a month of data ending today. For example, if today is Feb 6, 2019, that means I want to see results spanning the range from Jan 7, 2019 through Feb 6, 2019.This is a challenge with basic filters: "the past 1 month" refers to February and "the past 1 complete month" refers to January. We can look into the Advanced filter box and try "30 days ago for 30 days," which gets close, but is not dynamic based on the number of days in a month. The Solution Custom filters give us more flexibility by letting us reference now(), a timestamp for the current date in our filter condition.Given this, we can write a custom expression using Looker filter expressions that catches all dates a month before the current day of month, and catches all dates this month, before and on the current day of month. The expression is below: (extract_days(${history.created_date

5310

1 month ago

samLooker Staff

published in Technical Tips & Tricks

Filtering on the Last Day of the Month

Looker will not be updating this content, nor guarantees that everything is up-to-date. Filtering on the last day of the month is very simple with the right logic. The key is to think of the last day of the month as the day on which you can say, "tomorrow is the first day of the month."Method 1: Custom FilterTo show only dates that are the last day of the month, we can use a custom filter:extract_days(add_days(1,${date})) = 1The above expression returns true if "tomorrow is the first of the month," which is another way of saying "today is the last day of the month." Method 2: LookMLWe can take the logic of the custom filter above and convert it to SQL in a type: yesno dimension. This example uses Redshift:dimension: is_last_day_of_month { type: yesno sql: EXTRACT( day from DATEADD(day,1,${date_raw}) ) = 1 ;;}Here we use the raw timeframe, because it directly inserts the database column without any extra formatting.SQL For Various DialectsThe SQL you use in the dimension varies by dat

4760

1 month ago

quinn.morrisonLooker Staff

published in Technical Tips & Tricks

Calculating Moving Averages

Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 21.12, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when the Custom Fields Labs feature is enabled, and you have permission to use them. Creating a Moving Average with Table CalcsSometimes we want to display overall trends in our data more clearly, using moving (or rolling) averages. We can create flexible moving averages with table calculations in Looker, using the function offset_list(). This will return a list of values in a specified window, which we can then average.To create a 7-day moving average, we will use the following calculation:mean(offset_list(${field_being_averaged},0,7)) offset_list takes three arguments: the column from which you want to grab the offset values, how far from the current row you want to start the offset, and how long you want the off

19940

1 month ago

Page 2 / 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 do I render a local image in Looker?Knowledge Drop

Troubleshooting Private Embedding + SAMLKnowledge Drop

How do I hide one of my pivot columns?Knowledge 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