Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

Looker Connect Training

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

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

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

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.

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

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,

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

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

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

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

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

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,

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,

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,

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,

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,

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,

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

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

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

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

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

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

samLooker Staff

published in Technical Tips & Tricks

Computing the Difference Between Dates (for Non-Developers)

Looker will not be updating this content, nor guarantees that everything is up-to-date. If you have developer access, check out the documentation on using the type: duration parameter with a dimension group to easily calculate interval-based durations. The ProblemI have two dates and I would like to know how much time has elapsed between them. I might want to know this result in days, months, or years. The Solution Looker's table calculations offer a simple way to compute the difference between two dates on the fly. The diff_days family of functions compares two dates and returns the difference between them as an integer.Example 1: Difference Between Two FieldsEach of the orders in the table below has a created and returned date. We can use a table calculation with the diff_days function to compute the number of days that elapsed between purchase and return:diff_days(${orders.created_date},${order_items.return_date}) Example 2: User Account Age Each of the users listed below has a sig

maxcorbinLooker Staff

published in Technical Tips & Tricks

How to Optimize SQL with EXPLAIN

Looker will not be updating this content, nor guarantees that everything is up-to-date. This article discusses the EXPLAIN command, which is not supported by all database dialects. Be sure to check the Feature Support section in the Looker Database Configuration Instructions specific to your database to confirm whether this function is supported by your database dialect.Very slow SQL queries are sometimes a fact of life for a database. The database could be running as fast as it can while those queries are transforming a lot of data, or the database could be performing certain tasks that are hard for SQL query planners to accomplish. However, sometimes it's possible to optimize these queries by looking at the steps involved in completing the query, and using this information to redesign the query to be faster. In many SQL dialects, this can be achieved by using the EXPLAIN command.This article gives a very brief introduction to and an example of how to interpret the results of the EXPL

seanhigginsNew Member

published in Technical Tips & Tricks

Subtotals with Table Calculations

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. This article shows a method to generate subtotals with a standard Looker table visualization. In Looker 6.10, the Table-Next visualization was introduced, which can generate subtotals natively. For more information, see this documentation page.Subtotals are handy for a quick summary of grouped items, when you are also viewing from a higher level, such as subtotal of sales by brand, while also viewing the overall inventory sales. This article will show you a breakdown process on how to accomplish this. This is a stopgap solution, so check out this awesome Community post about generating Subtotals with a CROSS JOIN pattern. It provide

1
2
3
4
5
6
7
8
...
85

Page 5 / 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

Powered by inSided

Terms of Service

Sign up

Already have an account? Login

Social Login

or

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

or

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.

OK

This file cannot be downloaded

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

OK

© 2022 Google. All rights reserved.
Privacy | Terms | Community Guidelines