Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Technical Tips & Tricks

Previously internal product knowledge now available for you to explore!

  • 1,744 Topics
  • 404 Replies

1744 Topics

Vincent
Looker Staff
VincentLooker Staff
published in Technical Tips & Tricks

What kinds of named value formats can I use?Knowledge Drop

Last tested: Jan 21, 2019 This Doc has the defaults.With named_value_format, you can give it any kind of excel style formatting you want. Here's the doc that shows how to set them up and some common excel style formats.Tip: At this time date formatting, color formatting, and hexadecimal conversion ARE NOT supported in Looker. This content is subject to limited support.                  

650
Vincent
Looker Staff
1 year ago
Taylor_O_Meara
Looker Staff
Taylor_O_MearaLooker Staff
published in Technical Tips & Tricks

ERROR: Measures with Looker aggregations (sum, average, min, max, list types) may not reference other measures.Knowledge Drop

Last Tested: Mar 16, 2019 The Error:You might see this error in the LookML validator or the explore.There is a related error from the same issue: "Error: aggregate function calls may not have nested aggregate or window function". What does this error mean?This error is because of a SQL limitation: nested aggregation. Most SQL dialects cannot do the following:SELECT user_id, AVG(SUM(sale_price)) FROM orders GROUP BY user_id Common Causes / Fixes:1.It's fine to have a measure that references another measure, but it must be type: number. That's because the original measure(s) that the new measure is based off is/are already doing an aggregation. Declaring a second aggregate function will cause nested aggregation. Instead, using type: number acts as a place holder so you can reference other measures or combinations of measures. Example:| measure: order_count { # Base measure #1type: countsql: ${order_id} ;;} measure: cancelled_orders { # Base measure #2type: countfilters: {field: statusval

22220
Taylor_O_Meara
Looker Staff
1 year ago
Vincent
Looker Staff
VincentLooker Staff
published in Technical Tips & Tricks

Could not find a set named ALL_FIELDS (all fields at the join level)Knowledge Drop

Last tested: Jan 21, 2019 That is expected, ALL_FIELDS can only be used on the explore level. This content is subject to limited support.                 

1910
Vincent
Looker Staff
1 year ago
Vincent
Looker Staff
VincentLooker Staff
published in Technical Tips & Tricks

Why wouldn’t a value_format_name or value_format apply to my field?Knowledge Drop

Last Tested: Dec 5, 2019 Without a type parameter, a field type defaults to string. You need to add type: number in most cases where this happens.You should change this:measure: ratio_of_cases_to_accounts {value_format_name: decimal_1sql: 1.0 * ${count} / ${accounts.count} ;;} to this:measure: ratio_of_cases_to_accounts {value_format_name: decimal_1type: numbersql: 1.0 * ${count} / ${accounts.count} ;;} This content is subject to limited support.                  

450
Vincent
Looker Staff
1 year ago
S
Looker Staff
swhiteLooker Staff
published in Technical Tips & Tricks

"Unknown field 'X' in explore Y" or "field_filter does not support dimensions outside of the current model" for field_filter.Knowledge Drop

Last Tested: Jan 21, 2019 TL/DR: check for include: *.dashboard in other model files. This error can pop up from normal Unknown Field reasons so check those too (missing join, fields: [], etc)The issue is that the dashboard is included in a model that doesn't contain the necessary joins. Usually you have an include: *.dashboard parameter in another model than the dashboard is usually referring to.You'll either want to comment out the include: or replace the *.dashboard with specific dashboard names. This content is subject to limited support.                 

3620
S
Looker Staff
1 year ago
S
Looker Staff
swhiteLooker Staff
published in Technical Tips & Tricks

Datatype parameter for dimension groupKnowledge Drop

Last Tested: Jul 3, 2018 The datatype parameter is a parameter for a dimension_group: of type: time.This parameter tells Looker what datatype to expect coming from the database and consequentially how to transform it into the timeframes you specify in the timeframes: parameter. eg. if the column is an epoch in the database put datatype: epoch.Link to Docs This content is subject to limited support.                

340
S
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

IDE Quick Search/SwitchKnowledge Drop

Last tested: Jan 21, 2019 Quickly search and switch between files in a LookML Project with⌘ + J (Mac) Ctrl + J (Windows)Searches across all files in the current LookML ProjectFuzzy matching allows letters to be skipped (e.g. “ordritms” will include “order_items” as a match) This content is subject to limited support.                  

220
romain.ducarrouge
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

Why are my filter suggestions showing up several times?Knowledge Drop

Last Tested: May 10, 2018 The ProblemWe are seeing the suggested values for filters appearing fine in the Explore, but once a value was selected in the filter the suggestions were still listing this particular value.The ReasonThe data in the underlying table may not be cleaned. Instead of a string being "string" it might actually be " string", with whitespaces. The SolutionTry adding a TRIM() to the code for the field!sql: TRIM(${TABLE}.columnname);; This content is subject to limited support.                 

200
romain.ducarrouge
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

Using Liquid to check if any field is used for filteringKnowledge Drop

Last tested: Jan 21, 2019 One way to check if any field from the explore is used as a filter we can use{% if fieldA._is_filtered or fieldB._is_filtered or ....... %}But that is long, hard coded and annoying to maintain, so not greatA more dynamic approach is to use some of the liquid parameter available in Looker:html:<ul><li>link: {{ link }}</li><li>split: {{ link | split:"f[" }}</li><li>{% assign array = link | split:"f[" %}second part array: {{ array.last }}</li><li>{% if array.last contains "date" %}result: logic a {% else %}result: logic b {% endif %}</li></ul>;; The code above is showing every step by step the process, you only really need a combination of the last two to get it working, but that way it helps the client see how each step performs.In this particular use case, client wanted to check is any date fields is used as a filter:1. We take the {{link}} value2. Then split to remove all the first part about selected

5530
romain.ducarrouge
Looker Staff
1 year ago
quinn.morrison
Looker Staff
quinn.morrisonLooker Staff
published in Technical Tips & Tricks

Is there a color palette designed for color blindness?Knowledge Drop

Last tested: Apr 10, 2019 Yes! The Dalton collection was designed with this intention (named after a famous guy John Dalton: https://enchroma.com/blogs/beyond-color/john-dalton-the-father-of-color-blindness --fun fact!). You can find all the color palettes here: https://docs.looker.com/exploring-data/visualizing-query-results/color-collections This content is subject to limited support.                 

3850
quinn.morrison
Looker Staff
1 year ago
romain.ducarrouge
Looker Staff
romain.ducarrougeLooker Staff
published in Technical Tips & Tricks

What is Unit Testing/ Data Validator/ Data Tests/Results Validator?Knowledge Drop

Last Tested: Sep 24, 2019 All three of these names refer to the same feature, which is called “data tests” (formerly referred to as unit testing). This is a feature released as part of Looker 6, generally available on version 6.20.This is essentially Unit testing for LookML, where you write a query, assert that the query’s result is expected! You can create data tests in model files, view files, or in separate, dedicated data test files.Below is an example of implementation:test: test_there_are_users { explore_source: users { column: count {} } assert: there_is_data { expression: ${users.count} > 100 ;; }} More detail here:https://docs.looker.com/reference/model-params/test?version=6.20https://docs.google.com/document/d/1G3fLBZduZwCURi9BOvYOMdFsssHQOlXga2MSP7UHvd0/edit# This content is subject to limited support.                 

6250
romain.ducarrouge
Looker Staff
1 year ago
quinn.morrison
Looker Staff
quinn.morrisonLooker Staff
published in Technical Tips & Tricks

How do I pass filter values with special characters (i.e. &) into liquid url?Knowledge Drop

Last tested: Jan 21, 2019 Add url_encode to your liquid variable (whether that's {{ value }} or {{ _filters }} ) like this:"https://myinstance.looker.com/dashboards/38?Category={{ _filters['view2.category'] | url_encode }}" This content is subject to limited support.                  

2670
quinn.morrison
Looker Staff
1 year ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

When you Clear Cache & Refresh, will that create a new query ID?Knowledge Drop

Last Tested: Jan 22, 2019 No. If the query remains the same, clear cache & refresh should not create a new query ID. However, it will generate a new History ID. This content is subject to limited support.                 

310
paola
Looker Staff
1 year ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

What are the default groups in Looker?Knowledge Drop

Last tested: Jan 21, 2019 In an open system the default groups areAll Users Users (with access to Shared Folders)In a closed system, there is no default group. This content is subject to limited support.                  

530
paola
Looker Staff
1 year ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

Do public URLs work with access filters?Knowledge Drop

Last Tested: Apr 29, 2019 If your Look is dependent on access filters, the public URL will not work. (This includes Google Spreadsheets and Excel imports). This content is subject to limited support.                 

260
paola
Looker Staff
1 year ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

What does unpin axis from zero do?Knowledge Drop

Last Tested: Sep 6, 2019 This visualization setting allows the chart to focus on the area for which data exists, rather than showing the full scale starting at zero.For example, the data below ranges from 80%-100%. If we have unpin axis from zero off, the visualization looks like:vs if we have unpin axis from zero on, the visualization looks like: This content is subject to limited support.                  

1130
paola
Looker Staff
1 year ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

Can I have my string table calculation to the right of my measures?Knowledge Drop

Last Tested: Sep 6, 2019 ProblemBy default, dimensions will always appear to the left of measures in tables-legacy. Even if we use a table calculation to reference the dimension, i.e. ${dimension_name}, the table calc will appear to the left of the measure.SolutionThe Table-next visualization type is the real supported solution here, as it allows columns to be dragged anywhere.WorkaroundWithout using table-next, the key is to include a measure in your table calculation so that the calculation is treated as a measure rather than a dimension.One way to accomplish this is by using an if function in a table calc. We could do: if(length(${dimension_name}) + ${measure} >= 0, ${dimension_name}, ${dimension_name})We're using the if statement so that we can include a measure in the calculation. Keep in mind that it also doesn't really matter if the condition in the if statement is true or false, since it will always return our string dimension.  This content is subject to limited support.   

270
paola
Looker Staff
1 year ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

How can I display a decimal number in hh:mm:ss format?Knowledge Drop

Last Tested: Jan 21, 2019 If your value is a number of seconds, divide the number by 86400.0 (the number of seconds in a day) and then use the value format hh:mm:ss.NOTE that this is like a time, and will show you the 24-hour time modded by day. So if you had 25 hours, it would show 01:00:00 rather than 25:00:00. You can use the day modifier dd:hh:mm:ss to include days, or use sql as described in this community post.Table calcs can get at this too - where my_time_difference is in seconds:concat (floor(${my_time_difference}/(60*60))," hours, ",floor(mod(${my_time_difference},(60*60))/(60))," minutes, ",mod(${my_time_difference},(60))," seconds.") This content is subject to limited support.                  

20560
paola
Looker Staff
1 year ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

Math Operators/Functions in LiquidKnowledge Drop

Last Tested: Nov 29, 2017 Math filters in Liquid allow you to apply mathematical tasks in Looker.Math filters can be linked and, as with any other filters, are applied in order of left to right. In the example below, minus is applied first, then times, and finally divided_by. {{ product.compare_at_price | minus: product.price | times: 100.0 | divided_by: product.compare_at_price }} Link to Shopify Doc This content is subject to limited support.                  

6150
paola
Looker Staff
1 year ago
noah.kawasaki
Looker Staff
noah.kawasakiLooker Staff
published in Technical Tips & Tricks

Liquid parameter._parameter_value of a yesno parameter returns true and falseKnowledge Drop

Last tested: Nov 5, 2020 In a liquid condition, the parameter._parameter_value variable returns the string that will evaluate to true or false in the given SQL dialect. In most dialects, that’s just 'true' and 'false', but in some it’s '1=1' or '(1=1)' for true and '1=0' or ('1=0') for false. It does NOT return Yes or No. So you can evaluate them like this:{% if foo._parameter_value == "true" %} {% if foo._parameter_value == "false" %}In a SQL statement, _parameter_value will evaluate directly as true or false, so you can evaluate it like this:CASE WHEN {{foo._parameter_value}} THEN 'something'CASE WHEN NOT {{foo._parameter_value}} THEN 'something'Note that our official recommendation in our Docs on yesno parameters is to avoid using yesno parameters in logical Liquid statements. This content is subject to limited support.                  

4900
noah.kawasaki
Looker Staff
1 year ago
paola
Looker Staff
paolaLooker Staff
published in Technical Tips & Tricks

Why is my drill modal not applying my case when filters?Knowledge Drop

Last tested: Jan 21, 2019 If your measure has a case when statement defined in the sql parameter and your drill modal is not filtering out those values, you'll most likely need to change the measure to use filters instead. Take a look at this community article, Discrepancy when drilling into measures, for an example. This content is subject to limited support.                 

620
paola
Looker Staff
1 year ago
Morgan
Looker Staff
MorganLooker Staff
published in Technical Tips & Tricks

Can you round down in table calculations?Knowledge Drop

Last tested: Jun 16, 2020 There is a floor function that will round a number down to the nearest integer. However, to round down to a certain number of decimal places, you will need some math.Use this formula and replace # with the number of decimal places you want to round down to:floor(${number}*power(10,#))/power(10,#) This content is subject to limited support.                  

18160
Morgan
Looker Staff
1 year ago
noah.kawasaki
Looker Staff
noah.kawasakiLooker Staff
published in Technical Tips & Tricks

Unsuccessful authentication. Please try again or contact an administrator.Knowledge Drop

Last Tested: Jul 23, 2020 This error when trying to sign in through a SAML provider can come from attempting to authenticate when a user does not have a value set for an attribute that is required by Looker (see https://docs.looker.com/admin-options/security/saml-auth#pairing_saml_attributes_with_looker_user_attributes)For Google Auth, required user attributes are not available, so the issue may be this instead. Real life scenario:Q: You are trying to merge LDAP users to looker users.  (you have already created a user, gave email credentials using the API) But we are still seeing Unsuccessful authentication. Do the first and last name need to also match from LDAP to Looker?A: the merge only looks at the email - but a failed merge would not cause an unsuccessful authentication, it would cause two users. So there's something wrong with the LDAP settings that's preventing users from being able to authenticateRunning the test in the LDAP settings page will show more information on what's m

8840
noah.kawasaki
Looker Staff
1 year ago
Morgan
Looker Staff
MorganLooker Staff
published in Technical Tips & Tricks

"Key is already in use" when resetting git connectionKnowledge Drop

Last Tested: Dec 27, 2018 The ProblemWhen you reset a git connection, Looker provides the same deploy key as it did the first time the git connection was set up.The SolutionIf you're not changing remote git repositories, this means you don't need to re-add the key. It's already in your remote git repository settings.If you're trying to migrate from one remote git repo to another, then this collision can be solved one of two ways:Reset the deploy key on the Looker side. (This option is only available in Looker 5.0+) Delete the deploy key on the remote Git provider side on the repository that you're migrating from. This can be found in the repository settings page. This content is subject to limited support.                  

940
Morgan
Looker Staff
1 year ago
Morgan
Looker Staff
MorganLooker Staff
published in Technical Tips & Tricks

The Looker Explore URL ExplainedKnowledge Drop

Last Tested: Mar 17, 2019Consider this URL: https://learn.looker.com/explore/e_commerce/order_items?qid=9wqqjztJGVrVI6BZJejzTH&toggle=filhttps:// <- All Looker-hosted instances use SSL, which means that you need to use https instead of http.learn.looker.com/ <- Most hosted instances have the form hostname.looker.com. There are a few exceptions. explore/ <- this word is hard-coded saying that this page is an explore page.e_commerce/ <- this is the model name of the explore you're looking at. This is why you can't have duplicate model names, even across projects (note that the project name is not in the URL).order_items <- this is the name of the explore that you're looking at.? <- The question mark here means that we're adding a parameter to the URL. You can choose to leave off the question mark and everything after it to just navigate to the blank explore.qid=9wqqjztJGVrVI6BZJejzTH <- qid stands for query id. This is technically the query slug, not the query id

6400
Morgan
Looker Staff
1 year ago

Badges

  • Friendly Face
    sbrylowskihas earned the badge Friendly Face
  • Helpful
    carriehas earned the badge Helpful
  • Community Star
    carriehas earned the badge Community Star
  • Friendly Face
    carriehas earned the badge Friendly Face
  • Community Star
    Dawidhas earned the badge Community Star
Show all badges
Terms of Service