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
  • 406 Replies

1744 Topics

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

41212
sam
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
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.                  

1040
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
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 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.                 

9210
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
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

2480
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
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.                 

1260
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
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

3000
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
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.                 

640
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
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.                  

680
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
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.                  

260
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
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.                  

4560
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
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.                  

6310
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
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.                  

3110
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Custom liquid formatting isn't displaying in visualizationKnowledge Drop

Last Tested: Jan 21, 2019 DCL tip: To display custom liquid formatting, use {{ rendered_value }} instead of {{ value}}{{ value }} The output displays the standard result from querying your database. Used when datatypes don't matter Output of the code below will be only display the raw query data. (ie. 12345)measure: total_sales {type: sumsql: ${TABLE}.Total_Sale ;;html:<p style="font-size:50px;font-family:monotype;>{{value}}</p>;; value_format_name: usd_0}{{ rendered_value }} Output displays the results from querying your database with formatting applied to it. Used when datatypes do matter Output of the code below will display the query data with formatting applied to it. (ie. $12,345) *font size and family change carried to visualization*measure: total_sales {type: sumsql: ${TABLE}.Total_Sale ;;html:<p style="font-size:50px;font-family:monotype;>{{rendered_value}}</p>;; value_format_name: usd_0 } For more in-depth information about liquid variables check out

940
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

"Variable not found" error in liquid htmlKnowledge Drop

Last tested: Jul 3, 2018 One reason you might get this error in liquid is if you use {{ }} and {% %} at the same time, like this:{% if value > {{ search_latency_top_hr.limit_95._value }} %}The appropriate way to do this is this:{% if value > search_latency_top_hr.limit_95._value %}Docs This content is subject to limited support.                  

720
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Conditional / Dynamic Value Format value_formatKnowledge Drop

Last tested: Jan 21, 2019 We can't do dynamic formatting using the value_format parameter, but we can using the html parameter! Here is an example that looks at a separate "currency" dimension and puts the proper symbol in front of our measure amount. html:{% if currency._value == 'EUR' %}€{{rendered_value}}{% elsif currency._value == 'USD' %}${{rendered_value}}{% else %}{{rendered_value}}{% endif %};; This content is subject to limited support.                  

10182
Philipp Hagmann
17 days ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Preserving drill fields when conditional formatting is appliedKnowledge Drop

Last tested: Jan 21, 2019 Tip: Use the <a></a> tag instead of your <p></p> or <div></div> tagWhen applying conditional formatting to the cells of your visualization using the HTML tag, you may lose your drill field functionality if not careful. To solve this use the <a></a> tag:dimension: status {type: stringsql: ${TABLE}.status ;;html:{% if value == 'complete' %}<a href="{{ link }}" style="color: white; background-color: lightgreen; font-size:100%; text-align:center">{{ rendered_value }}</a>{% elsif value == 'cancelled' %}<a href="{{ link }}" style="color: white; background-color: red; font-size:100%; text-align:center">{{ rendered_value }}</a>{% elsif value == 'pending' %}<a href="{{ link }}" style="color: white; background-color: orange; font-size:100%; text-align:center">{{ rendered_value }}</a>{% endif %};;drill_fields: [created_date] In our code above, we have applied used the <a></a>

3000
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
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

21472
V
8 months ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Is it possible to make the domain in the url of a `link` parameter dynamic, based on the user's instance environment (i.e. prod, dev, etc. )?Knowledge Drop

Last tested: Oct 25, 2019 Simple CaseIn the url of a link parameter, you can omit the instance name and Looker will fill in the instance name with the instance you're on when you click the link. So you can just use /dashboards/123 .User Attribute WorkaroundIf you want something dynamic that isn't just the current instance name, we can use a clever workaround.There is no liquid variable that can make the domain of a link dynamic, but you can workaround this by creating a user_attribute with the same name on each instance, but assigning it different default values. You will not assign this user attribute to anyone, but always use the default value. In the link parameter, you will hardcode the default value of the user attribute for each instance, like this:https://{{ _user_attributes['instance_hostname'] }}/dashboards/123 This content is subject to limited support.                 

1100
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Why are my stacked column series not maintaining a constant series order from low to high?Knowledge Drop

Last tested: Oct 26, 2018 Most likely you have chosen the Series Order option from the main EDIT > Plot menu. When using Stacked or Stacked Percentage plot (either column or bar), you have the choice to use:Default Descending AscendingDefault will maintain the constant series order for each column/bar. But Descending/Ascending will order each column from low to high or vice-versa. So you could see Series A at the top of one column but at the bottom of another column.More details on our Chart Options in our Documentation here. This content is subject to limited support.                 

340
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Why aren't all my zipcodes being visualized in the interactive map viz?Knowledge Drop

Last tested: Jan 21, 2019 There are a couple of possible reasons for this. First,  the built-in us_zipcode_tabulation_areas map layer used to visualize zipcode data in the interactive map is built from 2010 census data. It's possible that some of the zipcodes not showing up were created more recently (new zips are created all the time).Second, the map layer visualizes ZCTAs, as opposed to ZIPs. The main distinction here is that zipcodes are delivery codes, whereas ZCTAs are geographical areas. ZCTAs roughly map zipcodes to corresponding geographic areas, but the correspondence is not 1:1. Certain ZIPs correspond with non-geographic areas (for example, PO Boxes) as mentioned in this note in the docs. So, when using ZIPs, as opposed to ZCTAs, some data points may not be visualized. This content is subject to limited support.                  

1090
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Why can I drill even though I don't have the see_drill_overlay permission?Knowledge Drop

Last tested: May 29, 2019 Within Looker, the see_drill_overlay permission is included in the explore permission, so if a user has the explore permission, they will be able to drill by default.It's the second sentence in the permission list:https://docs.looker.com/admin-options/settings/roles#see_drill_overlay This content is subject to limited support.                  

1930
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Why can't I "hide no's from visualization" in a table calc?Knowledge Drop

Last tested: Sep 11, 2019 Double check to see if the Yes and No values have quotes wrapped around them!Note that if(${field_name} > x, "Yes", "No") is not the same as if(${field_name} > x, yes, no). If we include quotes around the yes and no values, Looker will output strings and we'll lose the ability to select Hide No's From Visualization:See how the option doesn't populate in the table calc settings? Now when we remove the quotes and use raw yesno values:Take note of the purple/pink values! It's how you know you're doing it right. This content is subject to limited support.                  

10290
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

What happens when a null is concatenated with a value?Knowledge Drop

Last tested: Jan 21, 2019 Just like arithmetic operations on a list, a null will make the entire result null. This content is subject to limited support.                  

840
Department of Customer Love
Looker Staff
1 year ago
Department of Customer Love
Looker Staff
Department of Customer LoveLooker Staff
published in Technical Tips & Tricks

Is it better to use the running_total measure or to use Table Calculations to perform a running_total?Knowledge Drop

Last tested: Jan 21, 2019 This can vary on your use case; however, Table Calculations provide more flexibility as they are run after your the Explore page returns results.This point is important to consider when your Explore results table has Pivoted Columns. Pivots will impact the Measure of `type: running_total`. In cases where a query is pivoted, running_total runs across the row instead of down the column. If this is not desired, add direction: column to the measure definition. This content is subject to limited support.                  

880
Department of Customer Love
Looker Staff
1 year ago
sam
Looker Staff
samLooker Staff
published in Technical Tips & Tricks

Conditional formatting (font size, color) in a table vizKnowledge Drop

Last tested: March 2021 There are currently options for font size in the table-next visualization.As of 4.10, we have conditional formatting that can be used in the table visualization. As of 5.2, this can be applied on a per-measure basis. This only affects colors, not font size.Size customization can be achieved with html in LookML, as in this article: dimension: status { sql: ${TABLE}.status ;; html: {% if value == 'Paid' %} <p style="color: black; background-color: lightblue; font-size:100%; text-align:center">{{ rendered_value }}</p> {% elsif value == 'Shipped' %} <p style="color: black; background-color: lightgreen; font-size:100%; text-align:center">{{ rendered_value }}</p> {% else %} <p style="color: black; background-color: orange; font-size:100%; text-align:center">{{ rendered_value }}</p> {% endif %};; } This content is subject to limited support.                

5870
sam
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