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

tigLooker Staff

published in Technical Tips & Tricks

Advanced Templated Filters

Looker will not be updating this content, nor guarantees that everything is up-to-date. This page assumes you understand the Templated Filters and Parameters page in Looker Docs. Be sure to check out the parameter discussion on that page, because using a parameter field is a better approach for many use cases.The advanced templated filter applications discussed below are useful for a few use cases, such as making a derived table obey top-level filters for performance or aggregation reasons, creating dynamic filtered dimensions and measures, and creating dynamic WHERE clauses in queries. Referencing a Dimension Instead of a Filter in Templated Filter TagsIn this example, we have a filter field to accept user input:filter: my_filter_name { ... }This filter dynamically modifies the derived table's SQL, like so:{% condition my_filter_name %} ...Alternatively, a dimension filter can also be referenced in a templated filter tag. For example, you can define a dimension:dimension: my_dimensio

samLooker Staff

published in Technical Tips & Tricks

How to Compute the Number of Days in a Month

Looker will not be updating this content, nor guarantees that everything is up-to-date. There are several situations where you might want to get the number of days in a given month.You might try to do this by hard-coding the number of days for each month into a new dimension with a 12-case CASE WHEN, but then you'd also need to handle leap years, where the number of days in February changes.A better solution is to write a table calculation that can dynamically account for leap years. The Table CalculationGiven a date dimension ${date} (where you would replace this placeholder with the name of your actual date field), this calculation returns the number of days in the month of ${date}:extract_days(add_days(-1, date(extract_years(add_months(1, ${date})), extract_months(add_months(1, ${date})), 1)))This calculation starts with the first day of the next month. The calculation then moves back one day, checks the day of the month, and returns the number of that day. The In-Depth ExplanationF

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

"Circular reference detected"Knowledge Drop

Last tested: Dec 16, 2020 Make sure you're not referencing the LookML field instead of the tables field. This example you can see that the measures sql is referencing this measure itself within the sql which causes the loop;measure: memberships {description: ""type: sumsql: ${memberships} ;;} What you should have is the ${TABLE}.field_name referencing:measure: memberships {description: ""type: sumsql: ${TABLE}.ended_memberships ;;} This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can I download a dashboard as a CSV or PDF via the API?Knowledge Drop

Last tested: Sep 27, 2019 For PDF: Yes. Use create_render_task() to schedule a job to render a dashboard (or other content) as a PDF file.Use render_task(id) to check the status of the render task. When it completes, use render_task_results() to fetch the PDF file.Note that PDF files are binary data, not JSON text. Client apps calling the render_task_results() may need to take special steps to fetch the HTTP response body as binary data, not as json text.For CSV of dashboard data: Yes, with some work. We don't offer an API to run all queries for a dashboard and return all the results. What you can do is fetch the dashboard, iterate through its tiles and find all the queries it refers to, and run those queries via the API, in CSV format. Don't forget to apply filters!If you are familiar with the SDKs, there are some examples there as well:Python: https://github.com/looker-open-source/sdk-codegen/blob/master/examples/python/download_dashboard_pdf.pyRuby: https://github.com/looker-open-so

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Embedding Looker Content in Salesforce

Looker will not be updating this content, nor guarantees that everything is up-to-date. In order to embed content in Salesforce, you will need to have Visualforce development permissions and the ability to edit pages and objects in SFDC.This post covers an advanced topic that assumes a familiarity with embedding Looker content. Embedding dashboards and Looks in the applications and platforms where your users spend their time is one of the most powerful ways to leverage Looker. This Help Center article expands upon the concepts addressed in How to Quickly Embed Looker in the Tools and Apps You Use, to provide guidance on embedding content within Salesforce. Adding a New Visualforce Page The examples below use the private embed method, which requires each user to be signed into Looker to be able to view content. Alternatively, you can make content available to all users regardless of Looker access by using the public embed method for Looks.The first step to embedding content in Salesfor

chris.seymourLooker Staff

published in Technical Tips & Tricks

In SQL, do I use double quotes or single quotes for string literals? What about for column names?Knowledge Drop

Last tested: Mar 14, 2019 It depends on the dialect. In some dialects, such as MySQL, you can use single and double quotes interchangeably for string literals or column names. In other dialects, such as BigQuery Standard, you can use them interchangeably for string literals, but must use double quotes for column names. In still other dialects, such as Snowflake and Redshift, you must use single quotes for strings and double quotes for column names.In general, using single quotes for strings and double quotes for column names is accepted for all SQL dialects, so that approach is the best practice. Remember this mnemonic:[S]ingle quotes are for [S]trings ; [D]ouble quotes are for [D]atabase identifiers. SourceExamples of errors you may see if you do not adhere to this standard:SELECT "1"Snowflake: SQL compilation error: error line 1 at position 7 invalid identifier '"1"'Redshift: ERROR: column "1" does not existSELECT 1 AS '1'Snowflake: SQL compilation error: syntax error line 1 at posit

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Advanced LookML - Hub and Spoke modelingKnowledge Drop

Last tested: Dec 18, 2019 One of the core benefits of LookML is that it stops you from repeating yourself when doing data analysis. Whereas previously you may have saved snippets of SQL queries, bits of business logic you knew you might need again, this process can be error-prone and in a big organization, this doesn’t scale well. With LookML, you write down those snippets just once into universal and reusable query components that are easier to manage. In computer science, this concept is called D.R.Y., or Don’t Repeat Yourself, and it can save you a lot of time (and tears). The mantra is to write the code ahead of time, not every time.What?In large enterprises, the deployment of Looker can grow to a size where the core data team can no longer manage every department’s data needs, and some level of delegation must happen. Splitting up projects so that each department can manage their own becomes advantageous, but how do you maintain consistency?In the hub and spokes architecture, a ce

samLooker Staff

published in Technical Tips & Tricks

Custom Filters: Exactly 1 Month Ago

Looker will not be updating this content, nor guarantees that everything is up-to-date. The Problem When analyzing data, you might want to see a month of data ending today. For example, if today is Feb 6, 2019, that means I want to see results spanning the range from Jan 7, 2019 through Feb 6, 2019.This is a challenge with basic filters: "the past 1 month" refers to February and "the past 1 complete month" refers to January. We can look into the Advanced filter box and try "30 days ago for 30 days," which gets close, but is not dynamic based on the number of days in a month. The Solution Custom filters give us more flexibility by letting us reference now(), a timestamp for the current date in our filter condition.Given this, we can write a custom expression using Looker filter expressions that catches all dates a month before the current day of month, and catches all dates this month, before and on the current day of month. The expression is below: (extract_days(${history.created_date

samLooker Staff

published in Technical Tips & Tricks

SQL Error: "X is not a valid group by expression"Knowledge Drop

Last tested: March 2021 This SQL error means that that database is trying to group on something that it can't. Usually, this means that there are aggregates in a dimension definition.To troubleshoot, first try to isolate the field that throws the error. Then take a look at its LookML definition. One example of aggregation inside dimensions that could cause this is:dimension: bad_dimension {type: numbersql: SUM(${other_dimension}) ;;}The solution is to only do aggregations in measures. This example should instead be a measure:measure: sum_measure {type: sumsql: ${other_dimension} ;;}If you need aggregation to happen in a dimension, so that you can pivot or group by it for example, then see dimensionalizing a measure. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

How to apply a filter or a sort to a drill-down field?Knowledge Drop

Last tested: May 13, 2020 If we want to drill down on a specific field, but apply an additional filter we can do this using the link parameter. We can build out a link structure similar to a linked explore or dashboard as explained in this article.Say we want to drill down on count, but only see fields for male users. We can use the link parameter and the f[viewname.fieldname]= syntax.measure: count {type: countdrill_fields: [id, state, orders.id, inventory_items.id, city, gender]link: {label: "Drill" url:"{{ filterdrill_count._link }}&f[users.gender]=m" }} The easiest way to do this is to create a normal drill.Click explore from here.Apply the filter.Click Share and grab the expanded filter.Decode the URL and copy the filter syntax.Then, apply this to the link parameter.You are done!If we want to apply a sort_by we can follow the same process. This content is subject to limited support.

Zach_AragosaLooker Staff

published in Technical Tips & Tricks

Dynamic Rank in Visualization Using Parameter

Looker will not be updating this content, nor guarantees that everything is up-to-date. This article requires knowledge of Liquid variables — specifically, {% parameter parameter_name %}. Using a LookML parameter called parameter, you can let users choose the top N ranking to display in visualizations on a dashboard. For example, if a user wanted to see the top seven product categories by total sale price, they could enter that value in a filter and the results would update dynamically:The ProcessTo implement the above solution, we need to perform the following steps: Add a type: number dimension and parameter to the appropriate LookML view (typically the same view that contains the fields you would like to visualize into rankings). The parameter will surface as a front-end filter for users who want to specify the desired number for rank, and the dimension will reflect that input to control how many data points are displayed in the visualization. The user's parameter value is capture

jamesnestlerLooker Staff

published in Technical Tips & Tricks

Tabbed Dashboard using Liquid HTMLKnowledge Drop

Last tested: Mar 1, 2021 The code snippets below would go in a text tile on your dashboard. The idea is you can break up larger dashboards into multiple “Tabs” while keeping content in one place from the end user’s perspective. The end user sees a clickable navigation bar on your dashboard. Basic Version:<hr><div type="button" class="btn btn-primary btn-lg btn-block">Text goes here</div><a href="path to next 'tab' in the dash here" class="btn btn-primary">Text goes here</a>#Add as many new <a href ... lines as you like for more tabs!Fancier Version:<hr><div class="vis-single-value"><div class="btn-group btn-group-sm"><a type="button" class="btn btn-primary btn-lg btn-block">Text goes here</a><a href="path to next 'tab' in the dash here" class="btn btn-primary">Text goes here</a>#Add as many new <a href ... lines as you like for more tabs!</div></div>Even better example, works on dashboard-next:&lt

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

jamesnestlerLooker Staff

published in Technical Tips & Tricks

How do I hide one of my pivot columns?Knowledge Drop

Last tested: Mar 16, 2020 If you find yourself here, +1 this feature request: https://feedback.us.pendo.io/app/#/case/17612We're here, and we want to hide the "other" column:We can accomplish this with pivot_index or pivot_where.You'll need to create a table calculation for each column you want to show, then hide the original measure.pivot_index(${your_measure_here},column_to_show)This ends up looking like this:And in the visualization looks like this: This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Templated Filters and Derived Tables - Variable not found "parameter"

We will not be updating Knowledge Drop content, nor do we guarantee that everything is up-to-date. The timestamp published on each Knowledge Drop denotes when it was last verified.Author: @Zam Derived tables are a common use case for applying templated filters. This is because templated filters allow us to restrict tables to certain criteria prior to building them.This approach is particularly useful in columnar databases, as they have a single sort order. Thus, it can be more efficient to isolate a subset of data from a large table as a joined subquery, and transform the subset on the fly. With this method, we can avoid joining in the entire large table.A great example use case of templated filters in derived tables is performing a dynamic cohort analysis, as detailed in the Help Center article [Analytic Block] Creating Custom Cohort Analysis. Referencing Dynamic Derived Table Views CorrectlyIn Looker, Explores and joins must reference the view name that is defined in the underlying

lindseyLooker Staff

published in Technical Tips & Tricks

Comparing a Previous Week to that Same Week Last Year

Looker will not be updating this content, nor guarantees that everything is up-to-date. It is common to compare things like the sales from a specific week of this year with the sales from the same week in previous years. Let's say you want to compare last week to the same week last year. There are two ways to accomplish this:Weeks Starting on MondayIf you are using Looker's default weeks starting on Monday, you can do this easily by creating a matches (advanced) filter on a date field (in the example below we use a week timeframe to show a count by week) with the following filter expression:1 week ago for 1 week, 53 weeks ago for 1 weekThis filter will output something like this:Note: We will need to turn off the dimension fill feature on the date field in the Explore to compare only the time periods that were filtered for. Otherwise, we may see NULLs in the result set for the count for filled-in dates.Weeks Not Starting on MondayIf you want to analyze weeks that start on a day other

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

In a table calculation, how do I fill all of the null values of a column with the last non-null value of that column before the current row?Knowledge Drop

Last Tested: Jan 21, 2019 index(${calculation_1}, max(offset_list(if(is_null(${calculation_1}), null, row()), -1 * row() + 1, row()))) There are a few steps in this formula:Get the row number for each non-null row using the row() function Use offset_list to get a list of all of those row numbers from the first row to the current row Take the max of that list of values, which will be the row number of the last non-null row Use the index function to get the value of the field for that row number This content is subject to limited support.

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

davidsLooker Staff

published in Technical Tips & Tricks

Git Deployment Strategies with Looker

Looker will not be updating this content, nor guarantees that everything is up-to-date. IntroductionThe purpose of this article is to help development teams choose a Git flow strategy that best suits their team structure, delivery cadence, and code review requirements.This article provides three flow examples, each with options for either a single instance or a multi-instance setup. The examples are presented in order of their complexity and rigor. Choose the option that achieves the right balance for your use case.The following three workflow examples are described in this article:GitHub workflow GitLab workflow Gitflow workflowA fourth workflow, for multiple Git repos, is included, but this approach is not recommended. Note: pushing to non-production branchesBy default, Looker will deploy all changes to a repository's default production branch. The Looker documentation on Getting your changes to production describes the options for modifying the default behavior, including the follow

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

My model is up to date with production, why do I get "Could not load model " when not in dev mode?Knowledge Drop

Last Tested: Jul 31, 2018That's likely because the model is not configured in the LookML Projects Page.Tip: Check the Manager LookML Project model configuration settings. The LookML model may not be allowed access to a certain DB connection.Another possibility is that local prod is not synced with remote, so the project may say "Up to date with production" in dev mode because it's up to date with remote prod but those changes still don't show up in local prod (this can happen particularly with new projects). If this happens, we can follow the instructions from this Help Center article on hitting the deploy webhook to get local prod synced up with remote prod. This content is subject to limited support.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

Can you save explores created from sql runner as a look or to a dashboard?Knowledge Drop

Last tested: Aug 9, 2019 It is not possible to do that in Looker at the moment. You can vote for a feature request here: Save a sql runner explore as a look or add to a dashboard.However, if the look is only accessible by Admins, you can use this workaround (remember, ONLY Admins, other users will get a model not found/some other error).For this, go to Admin-->Connections-->Explore from the cog. This will take you to a page where you can choose the table/view. That will eventually take you to the explore page (similar to what you would get from sql_runner). Choose your fields in this explore. You will now see the option to save as look/to dashboard from the cog!! This content is subject to limited support.

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.

Department of Customer LoveLooker Staff

published in Technical Tips & Tricks

What is a fanout?Knowledge Drop

Last Updated: Jan 21, 2019A "fanout" happens when you join two tables and a single value gets duplicated in the end result. This screws up aggregation since some of the values are being double counted. "Symmetric aggregates" was invented to prevent bad aggregation due to fanouts. This content is subject to limited support.

jesse.carahLooker Staff

published in Technical Tips & Tricks

Git Workflow Using Multiple Repositories Across Multiple Instances—Development, Staging, and Production

Looker will not be updating this content, nor guarantees that everything is up-to-date. Starting in Looker 7.12, you can deploy any Git commit SHA, tag, or branch to production with advanced deploy mode. This helps consolidate repositories in multi-environment developer workflows, where each environment points to a different version of a codebase. It also gives one or a few developers or administrators greater control over the changes that are deployed to production. MotivationBy setting up a Git workflow across multiple Looker host instances, you can create a dedicated development environment for developers while preventing end users from accessing experimental code. All development and code review is done in a development or staging instance. Once changes pass your team's quality assurance review, they are deployed to a staging or production instance as desired.This article describes how to set up a workflow among multiple instances and multiple Git repositories. If you have multiple

vishal.dharmadhikariLooker Staff

published in Technical Tips & Tricks

Why can't I see my explore in the Explore drop-down menu?Knowledge Drop

Last tested: Aug 31, 2020 First confirm that the Explore has been saved and then refresh the page to see if the explore is populating in the Explore dropdown.If the explore still isn't showing in the Explore menu, check if:The extension: required parameter is defined in the Explore. An Explore with extension: required is not visible to users on its own; it is intended only to act as a starting point to be extended by other Explores (documentation). The required_access_grants parameter is used on the Explore. If this parameter is used and a user doesn't have the necessary corresponding user attribute values, they won't be able to see the Explore in the dropdown menu (documentation). If the Explore is extending an Explore, check whether the base/extended Explore has hidden: yes defined.There is also a known issue in which if there is more than one model with the same name, the explore menu will sometimes show the explores from the unconfigured model instead of the explores from the confi

1
2
3
4
5
6
7
...
70

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