Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

The Kitchen Table

Explore the product knowledge we've built up together

2109 Topics

sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Why is my measure of type:count being made into a COUNT DISTINCT in the SQL?Knowledge Drop

Last tested: May 2020 A count measure usually creates a COUNT(*) in the SQL. However, if joins are involved, then a COUNT(*) would cause a fanout.So to avoid this, Looker generates a COUNT DISTINCT on the view's primary key instead. This should give you the same results as a COUNT(*) on that table with no joins. This content is subject to limited support.                

5211
C
8 months ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

How can I make one column have a conditional format style based on another column's value?Knowledge Drop

Last Tested: Jan 21, 2019 This is possible using Liquid in the html: of a given LookML field. The example is not exactly highlighted in this community article, but it can help for a reference point. For example:Articlehtml:{% if value == 'Shipped' or value == 'Complete' %} Using Liquid _valuehtml:{% if value == 'Shipped' or orders.state._value == 'California' %} Where _value is a Liquid variable that lets you refer to other values in the same row. See this page for more details. This content is subject to limited support.                  

5790
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Can I choose the value for comparison in a Single Value Visualization?Knowledge Drop

Last Tested: Jul 3, 2018 Not by default, but we can use the offset function to adjust what value is shown. For example:#comparison table calculation columnoffset(${column_name},-2) You can adjust the "-2" as either a positive or negative value to customize the value. This content is subject to limited support.                 

2040
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Are merge results cached?Knowledge Drop

Last Tested: Dec 6, 2018 As of 5.12 there is an option to Clear Cache and Refresh on the merged results page. Before 5.12, you had to click "Explore From Here" for each query and "Clear Cache & Refresh" each explore individually. Re-run the merge results and your data will reflect the refresh. This content is subject to limited support.                  

460
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

If we don't specify a `max_cache_age`on a datagroup, will it default to the 1 hour?Knowledge Drop

Last Tested: Jan 21, 2019 According to the datagroup docs, the cached results will be busted the next time the sql_trigger_value changes. So, no, the cached results will not expire in one hour.The cache age will, however, be affected by explore-level or model-level max_cache_age or persist_for values. This content is subject to limited support.                 

970
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

What's the difference between `add to dashboard` and `save to dashboard` options when creating a tile from a look?Knowledge Drop

Last Tested: Mar 15, 2021 Add to dashboard adds the look to the dashboard. These are therefore linked, and any changes to the look will update this look-based tile in the dashboard, and changes to the tile will update the look too.Save to dashboard adds the looks explore query (i.e. lookless tile) to the dashboard. Changes to the look, or to the tile in the dashboards will not effect each the other in this case.https://docs.looker.com/dashboards/creating-dashboards-beta#adding_query_or_look-linked_tiles_from_a_look This content is subject to limited support.                  

8551
teejay
3 months ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

My LookML Validator is slow!Knowledge Drop

Last tested: Jun 25, 2020 TL;DR:The bigger the project is, the longer it takes to validate. Quick wins:Be strategic about your "includes" Always including every other view may be easy for development but being strategic by using naming conventions to only include the necessary files will increase performance. Comment or delete unused explores (and joins, fields, etc) Consider splitting your project into multiple projectsCheck out this article for LookML best practices. Long answer:LookML project size (total number of lines) and unnecessary includes both affect the performance of the LookML validator.Validation is a two-step process; 1) the first pass of validation happens by loading every single file in the project, even the ones not referenced anywhere, into memory and does a full translation of the source code one model at a time looking for "compile" errors. 2) the second pass of validation starts at each model and then parses only the files referenced by that model. And then it bu

6662
IanT
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

How to create a running/rolling anything function (sum, mean...) down a column or across a pivot rowKnowledge Drop

Last tested: Jan 21, 2019 You can do this using a custom table calculation formula.Down a column:offset_list(${view_name.field_name},-1*row()+1,row()) Across a pivot row:pivot_offset_list(${view_name.field_name},-1*pivot_column()+1,pivot_column()) Just wrap your aggregation function around one of these formulas, and you're good to go!Examples:Running standard deviation down a column (yes, you can use this with any aggregation function available for table calculations!)stddev_samp(offset_list(${view_name.field_name},-1*row()+1,row())) Running total across a pivoted row:sum(pivot_offset_list(${order_items.count},-1*pivot_column()+1,pivot_column()))  This content is subject to limited support.                 

8030
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why is my percent or average measure showing zero (or rounding to an integer) aka where are my decimals?Knowledge Drop

Last tested: May 1, 2020 Here's a Help Center article to explain this question.ExplanationIf you divide two integers in SQL, you get an integer. Thus 1/2 = 0.WorkaroundThe workaround is to cast one integer to a float. An easy way to do that is to multiply by 1.0.It is very important that the multiplying by 1.0 happens before the division operation.Working examples:1.0 * ${count} / ${sum}${count} * 1.0 / ${sum}${count} / ( ${sum} *1.0)What won't work:${count} / ${sum} *1.01.0 * ($[count}/ ${sum})Note for Druid Database: ${count} * 1.0 / ${sum} will not work. Put 1.0 to the denominator instead ---> ${count} / (${sum} *1.0) Redshift Edge CaseDividing by two SUMs in Redshift seems to default to 4 places of precision, as noted in this Stackoverflow article. The workaround is to cast one of them to a higher numeric datatype. ${sum1}::numeric(19,6)/${sum2}This also happens to measures of type:average on Redshift when the field being averaged is an integer.Presto Edge CaseUsing 1.0 * ${vari

4840
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

"Internal Server Error" when savingKnowledge Drop

Last tested: Jun 23, 2020 Please add possible causes as you come across them in the comments!Check for ad blocker! Try incognito mode or a different browser- this will pinpoint extensions as the culprit. Syntax error in LookML also causes this issue sometimes ( such as json format error/ the issue with dimension etc..) This content is subject to limited support.                 

3931
S
2 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

What's the longest allowed filter? Is there a limit on characters or values?Knowledge Drop

Last tested: March 2021 In the Looker product/code, there are no maximums on filter values or length. Theoretically, you can enter as many as you want. However, Looker uses Puma, which limits the url characters to 10240 (source: Puma Code).In the browser, a long URL can break the browser. Stackoverflow cites ~2,000 characters as the lowest limit for a browser (IE/Edge). All other browsers support URLs with > 8000 characters. As long as the URL is hashed (like in an explore), this is not a problem, but dashboard filters are simply added to the URL at the time of this writing. These problems can be solved by putting the values into a yesno dimension and filtering on that. An example:dimension: id_matches_value_list { type: yesno sql: ${id} IN (value1, value2, ... );; }In the database, there is usually a limit on query length, which is relevant since the filters usually go into the WHERE clause. A common limit is 2^16 characters (65336). There's no known workaround for this. This conte

7200
sam
Looker Staff
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

How can I enter multiple filters into Parameters?Knowledge Drop

Last tested: May 2020 Parameters only accept a single value.In the event that you have a comma separated list like a,b,c in your filter, the parameter syntax {% parameter filter_field %} will bring that in as a single string "a,b,c" .Therefore, if you have multiple values, use templated filters instead.Using a filter field rather than a parameter field, you can enter multiple values and then use templated filters to build the proper SQL. For example, using the LookML:{% condition filter_field %} other_field {% endcondition %}while entering a , b , and c into filter_field in the UI, would generate the SQL:other_field IN (a,b,c) This content is subject to limited support.                

5381
sawyerkeels
7 months ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Why can't I access chat?Knowledge Drop

Last tested: April 2021 If you don't see the chat option in the Help Menu:Permissions: Anyone with the develop permission can come on chat. This means that admins have this capability too. Check if the user is missing the develop permission.License type: If you have a partner license type you will not have chat access starting April 1st 2021. As for trials, trials which were initiated after Jan 5th 2021 will not have chat feature toggled on. However, the trials that were initiated before that are being turned off in a phased manner. If you see the chat option in the Help Menu, but clicking on it doesn't bring up the chat widget:Browser extensions: If you do not see the chat option in the help menu, but clicking it does not bring up the chat widget, check in incognito mode. Often times we've seen extensions such as Ghostery cause issues with the chat window not showing up in the past.Firewalls: There could be something in your network blocking the Zendesk domain. Besides firewalls, you

5651
Pieter_DeVries
Looker Staff
1 month ago
sam
Looker Staff
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

23452
sam
Looker Staff
4 months ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Can you do nested if in liquid?Knowledge Drop

Last tested: Jan 21, 2019 Yes! Source though it may be less confusing if move one of the conditions into a new field like a yesno:{% if template == "index" or template == "page" %}{% assign correct_template = true %}{% endif %}{% if correct_template and settings.slideshow_enabled %}<div class="container main content">...</div>{% endif %}  This content is subject to limited support.                  

720
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

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

Last tested: May 14, 2020 When I drill into a measure, such as a count, I see what is filtered on, and then the query shows "no results"This 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.                 

5560
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why is Looker not writing the Join condition in the generated sql? (sql_on)Knowledge Drop

Last tested: Jan 21, 2019 In all likelihood the LookML model syntax is using sql: INSTEAD of sql_on: Confirm that in your join you are using sql_on and not sql.For example:explore: deals {join: fact {relationship: one_to_onesql_on: ${fact.offer_id} = ${deals.offer_id} ;;}join: lead {relationship: one_to_manysql_on: ${lead.offer_id} = ${deals.offer_id} ;;}join: provider_lead {relationship: one_to_onesql: ${lead.redirect_id} = ${provider_lead.redirect_id} ;;}  The 3rd Join statement is using sql: and that will cause Looker to generate the following sql:FROM dealsLEFT JOIN view.lead AS lead ON lead.offer_id = deals.offer_idlead.lead_id = provider_lead.lead_id  It should be [LEFT JOIN] lead.redirect_id = provider_lead.redirect_id.If you wanted to customize your join logic with sql make sure to write that in the parameter, such asjoin: provider_lead {relationship: one_to_onesql: RIGHT JOIN ${provider_lead.lead_id} ON ${lead.lead_id}= ${provider_lead.lead_id} ;;}  Otherwise, just change the

1590
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why isn't my dynamic liquid label changing when I change the filter value?Knowledge Drop

Last tested: Aug 30, 2018 Is it changing in the visualization? Keep in mind that when a value of a liquid variable used in the label parameter is changed on the explore page, the change will be reflected in the visualization, but NOT in the Results table or in the field picker.Example: You have a label parameter defined like this: label: "{% if _filters['orders2.status'] == ''%}'default'{% else %}{{_filters['orders2.status']}}{% endif %}" Before filtering on status (Results table and vis both say "default"):After filtering on status (Visualization now says "test"; Results table still says "default"): This content is subject to limited support.                 

890
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

What is the property key with respect to TopoJSON?Knowledge Drop

Last tested: Jan 21, 2019 When using TopoJSON to create a custom map layer or region, you may need to specify a property_key field. Note that the property_key is not required, but if it exists it will look like:map_layer: my_map_layer {file: "map.topojson"property_key: "NAME"}Within a TopoJSON file, the corresponding field will look like this:…{"arcs":[[[4710]],[[[4712]]...,"type":"MultiPolygon","properties":"AREA":0,"POP2005":0,"REGION":0,"NAME":"Taiwan","LON":120.946,"LAT":23.754}... Where "REGION", "NAME", "LON", "LAT" are all available property_key fields. The property_key parameter chooses which key to map to. If not called out, Looker will automatically map to a property key.Note: The property_key field is CASE SENSITIVE! You could have a custom map layer set up and referenced correctly, but the map may display blank if the property_key is case mismatched.This example below will not work with the "NAME" key in the TopoJSON example above.map_layer: my_map_layer {file: "map.topojso

2180
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

If using `from:` in an Explore or Join, what view name do I reference in my sql_on statement?Knowledge Drop

Last tested: Jan 21, 2019 If you are using the from: in an Explore or Join, you are essentially aliasing the from: view_name as the Explore name. So you should use the explore name, rather than the view name.For example,explore: my_explore_name {from: view_name_Ajoin: view_name_B {type: left_outerrelationship: many_to_onesql_on: ${my_explore_name.id} = ${view_name_B.id} ;;}} If you attempt to use sql_on: ${view_name_A.id} = ${view_name_B.id} ;;, you will see a "cannot find field" error message.Tip: If from is omitted, Looker will assume that the underlying name is the same as either the Explore or the Join view name. This content is subject to limited support.                 

210
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why are there dashboard links in my drill menu when I haven't defined any?Knowledge Drop

Last tested: Jan 22, 2019 Double check to see if you have the Labs feature Dashboards in Drill Menus enabled. It's an awesome feature that can tell if a dimension can be filled in as a filter in a dashboard and then suggests the drill based on that! This content is subject to limited support.                  

510
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Is there a character limit for content in the Admin Content Sidebar?Knowledge Drop

Last tested: Apr 29, 2019 Yes! 255 for the title and 255 for the body. This content is subject to limited support.                  

230
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Is there a a bubble visualization in Looker?Knowledge Drop

Last tested: Sep 6, 2019 As of 5.20, there is a "Size points by field" option for scatter charts that can act like a bubble chart. This content is subject to limited support.                  

3570
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Can I calculate day of year in Table Calcs?Knowledge Drop

Last tested: Jan 21, 2019 Yes, you can use the following expression:diff_days(date(extract_years(now()), 01, 01), now())+1Note the +1, without the additional +1 you will only be counting days between Jan 1st and Today, not counting Today. This content is subject to limited support.                  

2370
Department of Customer Love
Moderator
1 year ago
Department of Customer Love
Moderator
Department of Customer LoveModerator
published in Technical Tips & Tricks

Why does my date comparison in filters/table calculations lead to an "Invalid filter expression" error?Knowledge Drop

Last tested: Jan 21, 2019 This can happen if you are comparing a LookML Date field with just a date value in quotes " ". For example:${view_name.created_date}="2015-07-16" While that may work when comparing String values, this will NOT work with dates.The way to make that a valid date expression in Table Calculations and Custom Filters is:${view_name.created_date}=date(2015,07,16) Note the use of the date( ) function. This content is subject to limited support.                  

1490
Department of Customer Love
Moderator
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