Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

- - Knowledge base
Product Announcements
Events
Groups

Community
The Kitchen Table
Technical Tips & Tricks

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

1,744 Topics
402 Replies

When you subscribe we will email you when there is a new topic in this category

1744 Topics

Most views

Recently active Most replies Newest first

samLooker Staff

published in Technical Tips & Tricks

How to pass a filter condition to a derived table AND keep it in the main queryKnowledge Drop

Last tested: March 2021 The ProblemI need a condition to appear in the WHERE clause of my derived table, but I also need it to appear in the WHERE clause of my main Looker-generated query. So my final query should have the same condition in both places, like so :WITH derived_table AS ( SELECT * FROM table WHERE <condition>)SELECT * FROM derived_tableWHERE <condition>Solution 1: Using a filter fieldIn Docs, we have a great example of using a filter field to pass a condition into a derived table.view: customer_facts { derived_table: { sql: SELECT customer_id, SUM(sale_price) AS lifetime_spend FROM order WHERE {% condition order_region %} order.region {% endcondition %} ;; } filter: order_region { type: string }}However, this only passes the condition into the derived table. The condition will not appear in the main query.To have the condition appear in the main query, we take advantage of the fact that the sql parameter of a filter field is placed directly into the WHERE cla

62571

6 months 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

60790

8 months ago

ZamMember

published in Technical Tips & Tricks

Exporting Data to Google Sheets

Looker will not be updating this content, nor guarantees that everything is up-to-date. You can export data directly to Google Sheets using the Google Sheets action from the Looker Action Hub rather than implementing the method described in this article. See the Looker Actions — Google Sheets Help Center article for more information.Looker supports exporting a public Look to Google Sheets. In this post, we present some functions that make the process smoother. Basic setup Looker admins must enable the Public URLs setting.To obtain the function that enables you to import data from a Look into a Google Sheet:Navigate to the Look that you want to export to a Google Sheet. Click the gear menu and select Edit Settings. Copy the function under Google Spreadsheet. This function includes the ImportXML function that is built into Google Sheets. Paste the function into the upper-left cell of the Google spreadsheet where you would like your data to appear, and then press Enter.Refreshing your dat

54750

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

52990

8 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can you embed a Looker iframe in PowerPoint / Google Slides?Knowledge Drop

Last tested: Sep 8, 2020 Looking for a solution to export a dashboard of visualizations to a Google Slide deck on their template. Below were the options I was able to outline. Spoiler alert: none are ideal.(1) Python Script Overview Allows you to take a template slide deck and push viz images onto them Pros Most complete, detailed solution Works with Google Slides Very automatic solution once complete - would scale well Cons Requires significant dev time to create the solution Minor maintenance may be required to make changes over time (2) LiveSlides Overview Embed live visualizations into MS PPT presentation Pros Embed interactive visualizations into a PPT presentation Very easy to get set up Will update with live data with no work. No need to export images Cons Only available in MS PowerPoint Cannot be exported to PDF (3) Use a webhook tool like Zapier Overview Use a tool like Zapier to send a visualization from Looker and place it on slides Pr

50593

10 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How do I get an image or gif on my dashboard's text tile or project readme?Knowledge Drop

Last Tested: May 15, 2020 Option 1: IF YOU HAVE A PUBLICLY ACCESSIBLE URL TO THE IMAGEYou can do this by using html in the title, subtitle, or body parameter of your text tile. Note, that the image you'd like to display needs to be on the public internet (practically speaking, what this means is that if you can open a new incognito window and go to the image's direct link, you can reach it). Note, if an image is in a Github repo and the Github repo is public, you can always add `?raw=true` to the end of the image url to get the direct link for embedding.You can use the template below, changing out the value of the img src attribute:<img src="INSERT DIRECT LINK OF IMAGE OR GIF" width="100%" height="25%" border="0" /> Option 2: IF YOU DON'T HAVE A PUBLICLY ACCESSIBLE URL TO THE IMAGEIf the image you want to display isn't currently on the public internet, you'll need to upload it to some publicly accessible location.One quick way to do this is to upload the image file to imgur.com a

50380

1 year ago

swhiteLooker Staff

published in Technical Tips & Tricks

Redshift Schema/Table privileges — ERROR: permission denied for relationKnowledge Drop

Last tested: Oct 24, 2018 A weird quirk that happens specifically with Redshift is that when using a schema that isn't the Public one and tables are being updated with full table drops, every time a table is dropped and re-created it doesn't inherit the access permissions of the old table.This can cause users to get errors when trying to access the relation like this: ```ERROR: permission denied for relation``` If they've GRANTED privileges on the table, they probably only granted them on that version of the table which was dropped at some point for an update.There are (at least) 2 solutions for this problem. The simplest thing to do would be to make the looker DB user an owner of the new schema. That way any tables that are created will be fully accessible by the looker user and therefore Looker.If you are hesitant to give ownership of the schema to the looker user, the other option is to log in as the super user and run this command: ALTER DEFAULT PRIVILEGESFOR USER schema_owning_use

49940

1 year ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Show or hide value labels on a specific seriesKnowledge Drop

Last tested: Mar 31, 2021 There isn’t a native option in the visualization edit panel to toggle value labels on/off for individual series, but please leave feedback for the Looker product team! After authenticating into the feedback portal, you can click here for the open request!Two other options you can use below:Solution 1You can use the "Value Colors" input field to manipulate the coloring of each series by entering color codes or names and comma separating to target series in the order they appear from left to right. If you define the color as "transparent" it will hide the value label for the series! Caveat: "transparent" is only transparent to the underlying graphic, it overlaps with other value labels as "white." If you have overlapping value labels, make sure the series you want with "transparent" value labels is listed last.Solution 2Another workaround we can implement is to use the Excel-style formatting in the value format section. For example, if we write#.00%[>0.05];[&

49938

1 year ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

How to Change Colors and Fonts Using HTML

Looker will not be updating this content, nor guarantees that everything is up-to-date. By using HTML tags in LookML, it is possible to manipulate certain elements of table visualizations, such as font color or size. Using HTML tags and Liquid variables, you can build a dimension or measure with the style of your choice. Read about the html field parameter and Liquid variables on the html and Liquid variables documentation pages, respectively.For example, this dimension will display names in green:dimension: name { type: string sql: ${TABLE}.name ;; html: <font color="green">{{ value }}</font>;;} This can be helpful for highlighting specific values or conditions with conditional formatting. There is also an option for conditional formatting on the Formatting tab for table visualizations. To apply conditional formatting in your visualization, turn on the Enable Conditional Formatting feature to view the options. You can specify the color code for your visualizat

48000

8 months ago

molly.lippsettLooker Staff

published in Technical Tips & Tricks

List of workarounds for sorting pivots by measure / sorting by column totals / dynamic top n rankingKnowledge Drop

Last tested: Jul 13, 2018 The problemSay you want to see some metrics for your top 10 customers by number of users, where the customer dimension is pivoted. Currently there is no easy way from an explore to sort a pivoted dimension based on measure values—you can only sort the pivoted dimension alphanumerically. Some features requests have popped up related to this issue like:Sort by Row / Sort pivots by value Rank top N / Pivots sortable by measures Sort or limit pivot columns by top valuesThere are a few workarounds you can use.Workaround #1: Derived table with rank window functionThis is the approach used in a couple of help centre articles:Sort by Pivots, Rank with Other Bucket, and Percentile Tail Analysis Dynamic Rankings (Filtering Dimensions by Custom Rank)This is the most robust solution, but it requires some work to set up the query for the derived table. Workaround #2: Sort row totals and transposeThis workaround can only be used with a table visualisation and can only suppo

45871

1 year ago

jamesnestlerLooker Staff

published in Technical Tips & Tricks

ERROR: within group ORDER BY clauses for aggregate functions must be the same -- RedshiftKnowledge Drop

Last tested: May 6, 2020 You are probably either using multiple MEDIAN() functions working on different columns together, or are using multiple other sort based aggregate functions together.Per Redshift documentation:If a statement includes multiple calls to sort-based aggregate functions (LISTAGG, PERCENTILE_CONT, or MEDIAN), they must all use the same ORDER BY values. Note that MEDIAN applies an implicit order by on the expression value. So, if you have two measures like so:measure: bad1 {type: mediansql: column_1 ;;} measure: bad2 {type: mediansql: column_2 ;;}You'll receive the ERROR: within group by... error.To work around this:Do the MEDIAN() in a CTE Do the MEDIAN() in a subquery Only use one MEDIAN at a time Use PERCENTILE_CONT() window function in a measure type: number. Note: this approach is NOT recommended, see this post for the caveats and reasons why.Source, see 'Usage Notes' here: https://docs.aws.amazon.com/redshift/latest/dg/r_MEDIAN.html This content is subject to lim

42600

1 year ago

jamesnestlerLooker Staff

published in Technical Tips & Tricks

How Looker does timezones and how to troubleshoot themKnowledge Drop

Last tested: Feb 1, 2021 Timezones, yikes!You may need to troubleshoot timezones to answer questions like:Why isn't Looker converting timezones correctly? Why is the timezone CONVERT SQL wrong? Inaccurate? Is there a timezone conversion bug? My finance team says timezone conversions are incorrect because order X is missing from our Explore. Why is Looker not doing this correctly?The goal is this article is to help you understand how Looker does timezones and how that interacts with your database.How Looker does timezonesConnections Panel, Database Timezone: This is the timezone Looker THINKS dates are stored in. Will show up as one of the parameters in the SQL Looker generates to convert_timezone in generated SQL, on most dialects. Connections Panel, Query Timezone: This is the timezone Looker will attempt to convert timestamps TO. Shows up as the other parameter in the SQL Looker generates to convert_timezone. User Setting: User Timezone; "User Specific Timezones" related. This will r

42032

3 months ago

samLooker Staff

published in Technical Tips & Tricks

How can I split a string at a certain character with a Table Calculation or Custom Field? (split part a field by delimiter)Knowledge Drop

Last tested: Jun 1, 2020 You can split a value by a delimiter in Table Calculations and Custom Fields* by using the substring and position functions.In this example:We get the first position of the character b in the name string:position(${name},"b") Then we get everything before that position:substring(${name},0,position(${name},"b") -1 ) Alternatively, you could get everything after that position:substring(${name},position(${name},"b") +1 , length(${name})) *When using Custom Fields, the looker substring() expression is converted into the Looker generated sql, and most dialects do not allow negative integers for their Substring/SUBSTR/Position functions. So you'll want to wrap this expression in a conditional if() statement to make sure you address the scenario where the search string doesn't existWhen using in Custom Field (Custom Dimension)if(position(${name},"b")=0,${name},substring(${name},0,position(${name},"b") -1 )) This content is subject to limited support.

41861

1 year ago

samLooker Staff

published in Technical Tips & Tricks

Filter on this month but also see previous month's dataKnowledge Drop

Last tested: May 2020 The ProblemYou want to give your users a single date selector, where they can choose a month. Then, when they select a month, they should see data from that month and the previous month.Filtering for this month on a date dimension will remove any data from the past month, unfortunately. How to show both? The SolutionAssume ${date_raw} is the date you want to filter on. (Make sure to use the raw timeframe if it's a dimension group!)First, create a filter field for the user to interact with. Remember that a filter field doesn't immediately update the WHERE clause, but can be targeted with Liquid.filter: date_selector { type: date} Second, create a yesno dimension that captures the value of date_selector using liquid. This will be your "this month" indicator.dimension: is_selected_month { type: yesno sql: {% condition date_selector %} ${date_raw} {% endcondition %} ;;} Third, create a yesno dimension that captures the start and end of date_selector and subtracts a mo

41694

1 year ago

samLooker Staff

published in Technical Tips & Tricks

How to join the same table twice (self-join)Knowledge Drops

Last Tested: March 22, 2021In a given explore, each join name must be unique. To self-join the same table multiple times you can use the from parameter, as mentioned in the docs, to change the "join" name of the table.In this example, we join in person to order twice, with different names and different joining dimensions.explore: order { join: person { sql_on: ${order.customer_id} = ${person.id} ;; } join: representative { from: person sql_on: ${order.representative_id} = ${representative.id} ;; }}This content is subject to limited support.

40980

1 year ago

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

40860

8 months ago

samLooker Staff

published in Technical Tips & Tricks

How can I recover a deleted Look or Dashboard if the trash is emptied?Knowledge Drop

Last tested: March 2021 As of 4.8+, all deleted content goes to the Trash Folder. It stays there indefinitely until deleted by an admin. Any content can be restored from the Trash Folder by an admin.This card talks about how to find and restore a deleted Look or dashboard with i__looker, in the case where the deleted content has also been deleted from the Trash.If you accidentally deleted a Look or dashboard AND deleted the trash, it's possible to recover this Look or dashboard using Looker's internal model, i__looker. i__looker is the model underlying the Usage Panel in the Admin section.Recovering a deleted Look that is no longer in the trash Go to the history explore of the i__looker model. You can get there by navigating to a URL of the form: https://company.looker.com/explore/i__looker/historyReplace company.looker.com with your Looker instance's specific URL. If you know a specific time that the Look was queried, filter on History - Created Time for as specific a range as possi

40842

1 year ago

samLooker Staff

published in Technical Tips & Tricks

How do I change the order of my visualization/table?Knowledge Drop

Last tested: Dec 19, 2019 Table NextIn Table Next, you can freely drag columns around in the visualization.Table LegacyIn Table Legacy, the order of the visualization is dependent on the order of the columns in the data table. You can drag the columns to change the order of the series. However, you can't drag between different types of columns.Workaround for Table LegacyIt's true that you can't drag columns between different types of columns. There are 4 main types of columns:Dimensions Table Calculations based on dimensions ("green dimensions") Measures Table Calculations based on measures ("green measures") You can freely drag columns that are the same type between other columns of the same type.Thus, a workaround for reorganizing any column is to make all desired fields into "green measures"!To turn a measure into a green measure, simply make a new table calculation with the measure definition. ${measure} To turn a dimension or green dimension into a green measure, make a new tabl

40674

1 year ago

lindseyLooker Staff

published in Technical Tips & Tricks

How to count a non-primary key

Looker will not be updating this content, nor guarantees that everything is up-to-date. If you would like to count (not count_distinct) a field that is not the primary key, you can do this by creating a measure of type: number and performing the count in the sql parameter:measure: count_states { type: number sql: COUNT(${state}) ;;}This measure will generate the following SQL:COUNT(users.state)Measures of type: count_distinct can make use of a filters field parameter, while measures of type: number cannot. In addition, while this does not apply to COUNT DISTINCT, other aggregation types — like SUM, AVERAGE, and COUNT — should be represented with specific measure types rather than number.These difference allow Looker to properly apply symmetric aggregates in order to avoid fanout values when exploring and using aggregations.

40430

10 months ago

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Splitting Strings into Rows in the Absence of Table-Generating Functions

Looker will not be updating this content, nor guarantees that everything is up-to-date. IntroductionMost SQL dialects don't support robust table-generating functions. There are a few common use cases for table-generating functions, such as creating a sequence of numbers or dates, un-nesting a JSON object or array into rows, or splitting a string on a delimiter into rows.In this article, we'll show an example for the last use case: Taking a string containing n distinct comma-separated values and splitting it into n unique rows.SetupSuppose we have a table of products containing id, name, and tags: productsid name tags 123 Scrunch Cloth Pants Set Pants, Throwback, 80s 1287 Le Suit Citrus Breeze Skirt Suit Skirts, Dresses, Business Casual 486 MaxStudio Plaid Romper Jumpsuits, Casual, Rompers Our goal is to un-nest the tags to create a table to map products to tags. We will assume that a table of numbers already exists in the database, though this can be created using t

37953

4 days ago

samLooker Staff

published in Technical Tips & Tricks

Can I group or bucket by table calculations?Knowledge Drop

Last tested: March 2021 No. Table calculations are computed after SQL grouping, so it's not possible to group by them.Only dimensions can be grouped on. Consider using a custom dimension for quick grouping! https://docs.looker.com/exploring-data/adding-fields/custom-measure#creating_a_custom_dimension_using_a_looker_expression This content is subject to limited support.

37180

1 year ago

VincentLooker Staff

published in Technical Tips & Tricks

Can I change font size in visualizations?Knowledge Drop

Last Tested: Jul 11, 2019 It is currently not possible in all visualization styles. But this time we have font control over values in the following visualizations - Bar, Column, Scatterplot, Line, Area.We can also create a “single value visualization" out of a scatterplot which allows us to edit the displayed font size.However, we can use html tags to control font size for the time being.dimension: big_string { html: <p style="font-size:30px"> {{value}} </p> ;; type: string sql: ${TABLE}.string ;; } This content is subject to limited support.

37171

5 months ago

samLooker Staff

published in Technical Tips & Tricks

Page 2 / 70

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.