Looker Connect Training
Help Center
Documentation
Community
Cloud Certifications

Looker Connect Training

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
369 Replies

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

1744 Topics

Recently active

Most replies Most views Newest first

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Font Size - Single Value Visualization to ScatterplotKnowledge Drop

Last Tested: Aug 28, 2019 This is a workaround to enabling font size from the UI by creating a single value visualization out of a scatterplot.If you use a scatterplot visualization and do one of the following to return a single row in the results set,Limit rows to 1 Hide rows from visualization Filter for a single rowFrom here you can hide the point by changing the color of the point to white. You can do this by clicking on the color in Series > Customizations, and setting the hex code to #FFFFFF. Next you can change the color and font size to your desired style in Settings > Values (you'll need to set the color in order to see anything, since you've already turned it white).And there you have it, a font-size editable single value visualization with a very limited use-case. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Why might my moving average table calc plot differently than the moving average trend line?Knowledge Drop

Last tested: May 18, 2020 The trend line moving average includes nulls by treating them as 0, whereas (unless baked into your table calc) the table calc moving average will ignore nulls.For instance, suppose I had results for null, 3, 3. If I use something like mean(offset_list(${value},0,-3)), the denominator would NOT include nulls and the average would be 6/2 = 3.Conversely, if I use something like mean(offset_list(coalesce(${value},0),0,-3)) to replace nulls with 0s, the denominator would include nulls and the average would be 6/3 = 2.NOTE: Both methods are correct ways to calculate moving averages. To decide which one to use, you'll want to determine whether nulls represent 0's in your data or whether they should be ignored. If you want them to be treated as 0's, then you can use the trend line moving average. If you don't want them to be considered in the calculation, then you can use the table calculation that was suggested in this Community article.If there are no null values,

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How can I revert changes I pushed to production?Knowledge Drop

Last Tested: Feb 21, 2021 Option 1: Advanced Deploy From Specific Commit If you are using a Looker version that supports Advanced Deploy mode, use this method!Option 2: Manually rewrite LookML (“Anti-Commit” Method)In many cases, when a commit has changed relatively little, it's probably easier to look at what changed and just change things back. That is, make a new commit that undoes the work of the bad commit. You can look at what's changed in the remote provider. For example, in Github, click on Commits and each commit individually to look at what's changed. Deletions are shown in red and additions are in green. (If you have command line access, you can run a git log to show all commits and git show <SHA> to look at individual commit changes.) Chat Pro Tip: We recommend committing frequently so that the amount of work in each commit is relatively small. This makes anti-committing, if necessary, much simpler, as you only need to redo small chunks of work. This content is subje

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Why is there an extra field selected (and grouped) in my SQL?Knowledge Drop

Last Tested: Aug 3, 2018 Also asked as "Why do additional (extra) fields show up in the Select list and Group by when they have not been selected in the Explore"?This will happen if another field is referenced in a html or link parameter.Example:measure: recalls_percent {type: percent_of_totalsql: ${count} ;;link: {label: "{{make._value}} Details"url: "/dashboards/106?Make={{make._value}}"}} Generated SQL when only this measure is selected (note that my_table.MAKE is added to the query)SELECTmy_table.MAKE AS my_table_make,(COUNT(CASE WHEN (UPPER(my_table.TYPE ) = UPPER('RECALL')) THEN 1 ELSE NULL END)) AS my_table_recalls_percentFROM my_table Short answer, yes, because we need to populate the values for that link.Note: this is true of both link parameter and htmlThe solution is to create additional measures with or without the link as needed to avoid the additional GROUP BY This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can I explicitly exclude fields at the join level?Knowledge Drop

Last Tested: Sep 23, 2019 No - fields can be explicitly excluded at the explore level, but not the join level. You can thus exclude fields from a given join at the explore level by appropriately scoping the field name with the join name.At the join level, fields can only be explicitly included. If fields are specified in the fields parameter at the join level, each field not specified will be excluded.More information in this doc. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can I extend an explore from another project?Knowledge Drop

Last Tested: Mar 28, 2021 Yes you can, however this requires that the explores to be extended are defined in an explore file as including a model file from another project via local project import is not possible.To include an explore file from a different project (for example project_A in project_B), you must create a project manifest file, and define:project_name: "project_B"local_dependency: { project: "project_A"}Then use this pattern in the model file (your path to the explore file may differ, so if there are folders that the explore file is stored in be sure to include the folder in the path as well):include: "//source_project/*explore.lkml"This pattern works by prefixing the explore file with "*" to include all explore files (in the given path) from the source project. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Who/How to see connections in SQL RunnerKnowledge Drop

Last Tested: Oct 29, 2019 TL;DR: The model configuration controls SQL Runner connection access. The user needs the following to be able to see a connection in SQL Runner:They need a permission set that includes the use_sql_runner permission They need to have a model set that includes a model that is configured to access the connection This permission set and model set need to be combined into a single roleAn easy way to give the user access to ALL connections which have models configured for them is to give them the manage_models permission.Source:https://docs.looker.com/admin-options/settings/roles This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Is it possible to calculate row totals without a pivot?Knowledge Drop

Last Tested: Nov 23, 2018 The first instinct for users who are familiar with excel is to do (sum(${field1},${field2},${field3}))but you will notice that the result is not as expected and it does not sum the values in that row(). So, what is the difference?Excel sum does row specific total i.e, field1+field2+field3. However, when you use table calculation, it sums up across all the rows for the three columns specified. So it is a sum of all results for that field. Is there a workaround?There is a workaround that's a bit of a headache due to the amount of hardcoding.Take these results:If you want to perform aggregation across the measures (therefore calculating a row total), then you can use the following format:sum(list(${field1},${field2},${field3})) I.e.:sum(list(${order_items.count},${orders.count},${products.count})) This is an edge use case since measures typically perform different forms of aggregation (SUM,AVG, etc.) and it doesn't usually make sense to perform post-query aggrega

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Why do I see "No drill fields" when trying to drill from a visualization?Knowledge Drop

Last Tested: Apr 28, 2020 Possible reasons:If the associated measure(s) don't have defined drill_fields parameters, there are no drill fields available. Check the dimensions in the visualization to see if they use can_filter: no, this will kill drilling for any measure it is used with. If the measure uses html, this will override linking. A workaround can be found in https://community.looker.com/lookml-5/maintaining-drill-paths-for-custom-html-fields-6623 Check if the visualization was built on a table calculation rather than a measure. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Parsing JSON data in LookerKnowledge Drop

Last Tested: Apr 3, 2019 Looker doesn't have a native JSON field type.Workaround 1: SQL OperatorsYou'll want to use JSON parsing functions in SQL, like json_extract_path in Postgres and JSON_EXTRACT in BigQuery to extract the JSON and put it into a type that Looker can accept, like a string.There are a few Community articles where we have examples of doing this.https://community.looker.com/lookml-5/defining-json-objects-as-lookml-dimensions-2356https://community.looker.com/sql-10/using-postgres-and-json-functions-and-operators-to-parse-json-fields-1041NOTE: in BigQuery a JSON path must start with a $ followed by the index position and the string to parse, like: JSON_EXTRACT(${TABLE}.temperature_alerts, '$[0].description')Workaround 2: API ParsingHere is a cool open-source Python script which uses the Looker API to automatically detect JSON fields in the underlying database table and generates LookML for them (this is specific to Snowflake connections and may require adjusting to fit yo

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

What are these Unknown connections in my query queue?Knowledge Drop

Last tested: Jun 24, 2019 In the Admin > Database > Queries panel, sometimes we will see queries made to an unknown database without any information on the query.Usually these queries to "unknown" connections are placeholders for merged result queries. If a tile has results from two separate connections, we see 3 different events in the query panel. One placeholder (“unknown”), then two actual queries (with known connections listed). This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Why do I see "no results" when drilling from a measure?Knowledge Drop

Last tested: May 14, 2020 The ProblemWhen I drill into a measure, such as a count, I see what is filtered on, and then the query shows "no results"The SolutionThis can occur when there is an error in one of the fields being retrieved. Try "explore from here", or check the Admin -> Queries panel to see if there are any queries with errors. From there, the query can be opened in SQL runner to debug it.It could also be the case that there just aren't results. Check the drill_fields parameter for the measure and verify that the generated SQL should produce results in their data. (You can get the generated SQL for the drill in the Admin > Queries panel).Or if you enter a new row limit, hit run, then you can now see the results. This could be due to the drill querying having cached results. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Why would a dashboard filter change to matches(advanced)?Knowledge Drop

Last tested: Sep 28, 2020 If this is happening for some users only:We have seen this happen when a user doesn't have the model specified in a dashboard's field filter in their model set, or also when the model for that dashboard field filter hasn't been configured. In this case, the dropdown for the filter options will be missing—it won't actually say "matches (advanced)," although the filter value uses matches (advanced) syntax. For example, here is what the filter edit modal might look like for a user who doesn't have access to the field the "Date" filter is based on:Note that all there are blank entries for the model, explore, and field. Here is what it might look like on the dashboard:Notice that the filter options dropdown is missing.This happens because the default value of a filter is always stored as advanced syntax, which is then translated to the appropriate filter option based on the field type. But the type is part of the field, so the translation step won't happen if the u

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Where is my merged query id?Knowledge Drop

Last Tested: Sep 6, 2019 You can find the merged query id after the mid= in the url!For example, in this URL, https://master.dev.looker.com/merge?mid=5Pu6Q0FUz4ESXM23DwUQOi&toggle=vis, the merged query id is 5Pu6Q0FUz4ESXM23DwUQOi.Note: it's not currently possible to run merged queries via the API. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can you do a measure type: yesno?Knowledge Drop

Last tested: Jul 24, 2020 Yes you can!You can create yesno measures the same way that you create yesno dimensions. See https://docs.looker.com/reference/field-reference/measure-type-reference#yesno.When you filter on them, they go in the HAVING clause instead of the WHERE clause.However, don't use them in filtered measures. You can only filter on dimensions in filtered measures.Also, make sure they only reference other measures.Tip: to create a yesno measure from a yesno dimension (in this example, it's "yes" if any dimension value is yes), we can use something like this:measure : was_livetype: yesnosql: sum(case when ${is_live} then 1 else 0 end) > 0 ;; This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How does the persistent session feature work?Knowledge Drop

Last Tested: Jan 21, 2019 Disabling the persistent session feature will remove the checkbox that allows users to keep their login active for 30 days ("Remember me for 30 days" checkbox).Users will be logged out after 30 minutes or when their browser window closes without this feature.If the feature is switched off and was on before, then persistent sessions will end the next time the user uses that session and closes the browser.If the feature is switched on and was off before, then any user with an active session will have their session 'promoted' to a persistent one the next time they do anything in Looker, and subsequent user logins will be persistent. If the feature is off, anyone who is logged out while developing unsaved LookML will be greeted with a prompt to restore unsaved changes from a previous session when they navigate back to the LookML they were working on. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can you limit the number of accounts that can login to Looker if you are using Google Authentication?Knowledge Drop

Last Tested: Jul 3, 2018No, you cant. If you are using Google authentication, then anyone with a Google account in that domain will be able to create a user in Looker.However, if you set up authentication so that the default user role is empty, then they will have to be explicitly given permissions to do anything in Looker, they will just get an empty home page. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Column Chart OptionsKnowledge Drop

Last tested: Mar 31, 2021 This Doc talks about all the options for Column type visualizations.Each Column a Different ColorColors of columns in Looker's column visualizations are determined by series. One way to convert your single measure into a "series" is to pivot on the dimension. Each pivot is its own series and the colors can be set individually.While you can't individually color columns that belong to the same series, you can leave your feedback with the Product team here and tell them about your use case! This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

What is the column limit?Knowledge Drop

Last Tested: Aug 13, 2018 For unpivoted queries, there is no limit. You could potentially at any time build a result set that is wide enough to crash a browser if unpivoted. (The benchmark there is around 30,000 data cells.)We do apply a default of 50 columns (docs) and a hard limit of 200 columns when pivoting in order to try to control the amount of data. (Note that "column limit" here is a bit of a misnomer—the limit is actually 200 pivoted values. So if you have 2 measures selected, you can have up to 2 measures x 200 pivoted values = 400 columns. But this many columns could crash your browser.)If you need more than that, download the results unpivoted and then pivot in an external tool like Excel instead. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

What happens to a deleted user?Knowledge Drop

Last Tested: Sept 25, 2018 WARNING: Instead of deleting users, Looker Support recommends disabling them, which prevents the user from logging in but keeps all of their content, schedules, and data.When you delete a user, their personal folder is deleted and all of their Looks and dashboards in their personal folder will be moved to the trash (cite "Deleting Users" in Docs which has a nice table).You can recover the deleted content from the trash if you restore it before the trash is emptied by an Admin. If the content was originally in the deleted user's personal folder, when it is restored it will go into the folder of the admin who restores it.Any schedules that this user owned will also be deleted, so reassign schedules you want to keep before you delete the user. (tested on Looker 5.4)If you do choose to delete, we show a large warning message: This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can I see LookML dashboards in system activity?Knowledge Drop

Last Tested: Apr 2, 2020 Short answer is yes, but there's not much about them.LookML dashboards aren't stored entirely in the internal database, since they're defined in LookML (hence the name).However, some data resulting from the use of LookML dashboards are stored in the internal database and this is viewable through system activity in the history explore. Their dashboard ID will take the form model_name::dashboard_name rather than an integer.You can see LookML dashboard IDs show up in the history explore through this extended URL example:https://YOUR_INSTANCE_URL/explore/i__looker/history?fields=history.real_dash_id&f[history.real_dash_id]=%25%3A%3A%25&sorts=history.real_dash_id&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22history.real_dash_id%22%3A%5B%7B%22type%22%3A%22contains%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22%3A%3A%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&origin=s

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

SSO Embed URL ParametersKnowledge Drop

Last Tested: Jun 17, 2019 All SSO embed parameters can be found here!nonce is a value that identifies this request (16 character hexadecimal string). It shouldn’t be re-used (it’s a single sign-on) and should come from by a secure random number generator. time is a timestamp to identify when the session begins. session_length is how long a user should be authenticated (in seconds). Pick something reasonable (no more than 30 days) external_user_id is Looker instance user ID of the user being authenticated - customers should be using a unique identifier for their users. This is how Looker maps SSO uri’s to actual Looker users. This is not the user id in Looker and can be a string. permissionsshould be a grouping of Looker permissions, comma separated list of single quoted elements models is a group of models your user will have access to. Make sure they’re spelled correctly and in quotes. group_ids: This is a list of the actual id of groups in Looker. You may leave this empty if you wan

margaret.abercrombieLooker Staff

published in Technical Tips & Tricks

Why is my Conditional Formatting ignoring values?Knowledge Drop

Last tested: Aug 23, 2019 If this is a percent calculation, first check if the percent formatting is applied to a decimal value. Conditional formatting applies to the raw values, so if the formatting is applied on the front end, try changing the conditional formatting rule value to decimal format (ex: 0.7 instead of 70%). See Why isn't my Conditional Formatting rule applying to my Percent values in table visuals/table calculations? for more detail.If formatting the formatting rule is defined correctly, check if "Limit Displayed Rows" is enabled.Currently, conditionally formatting is applying formatting to the order of rows in the returned data, not the visualization. When rows are hidden from the visualization, this causes a misalignment and it looks like the conditional formatting is not correctly applying the rules. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can I use the IN operator in table calcs or custom fields?Knowledge Drop

Last Tested: May 3, 2019 This is a feature request: https://feedback.us.pendo.io/app/#/case/17680 Workarounds Custom FieldsYou can do it with the matches_filter function in custom fields, which basically lets you replicate a "matches advanced" filter string.matches_filter(${view_name.field_name}, `string1,string2,...`) Table CalculationsTry this:contains(concat("California~", "Alabama~", "Alaska~", "Hawaii"),${users.state}) position(concat(list("Value1", "Value2", "Value3"), ","), ${view.field_name})>0 This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can you use the field value as the else in a CASE?Knowledge Drop

Last tested: Sep 7, 2019 No, not in the Looker Case-When statement!However, you can accomplish this in the sql parameter with a CASE WHEN statement.Example:dimension: country_bucketed {type: stringsql: CASE WHEN ${country} = 'United States'THEN 'United States of America'ELSE ${country}END;; } Note: If you leave out the "else" condition with a Looker case parameter, Looker will bucket everything else as a null This content is subject to limited support.

1
...
58
59
60
61
62
63
64
...
70

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

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