Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Topics started by Pieter_DeVries

15 Topics

Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

Create Day of Quarter in Table CalcKnowledge Drop

Last tested: Jun 15, 2021 This requires 3 easy steps of Table Calc magic.Here is a picture to get an idea.The steps are belowCreate an extract months table calcextract_months(${table.date}) Create an extract days table calcextract_days(${table.date}) Create Days of Quarter, referencing the month and adding days to the days based on quarter.if(${month} = 2, ${day_of_month} + 31, if(${month} = 3, ${day_of_month} + 31 + 28, if(${month} = 5, ${day_of_month} + 31, if(${month} = 6, ${day_of_month} + 30 + 31, if(${month} = 8, ${day_of_month} + 31, if(${month} = 9, ${day_of_month} + 31 + 30, if(${month} = 11, ${day_of_month} + 31, if(${month} = 12, ${day_of_month} + 31 + 30, ${day_of_month}))))))))The above is a potential use case for this! Good luck! This content is subject to limited support.                  

3063
Kyle_Webb
7 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
asked in Technical Tips & Tricks

More than three conditional/dynamic value formats. INCLUDING negative numbers

Those sweet sweet Bs Ms and Ks Huge shoutout goes to @Hugo_Selbie who came up with this formula. dimension: value { sql: CAST(${TABLE}.value as integer) ;; type: number html: {% if value > 1000000000 %} ${{value | divided_by: 1000000000 | round:2 }}B {% elsif value >= 1000000 and value < 1000000000 %} ${{value | divided_by: 1000000 | round:2 }}M {% elsif value >= 1000 and value < 1000000 %} ${{value | divided_by: 1000 | round:2 }}K {% elsif value >= 0 and value < 1000 %} ${{value | round:2 }} {% elsif value > -1000 and value < 0 %} ${{value | round:2 }} {% elsif value > -1000000 and value <= -1000 %} ${{value | divided_by: 1000 | round:2 }}k {% elsif value > -1000000000 and value <= -1000000 %} ${{value | divided_by: 1000000 | round:2 }}M {% elsif value <= -1000000000 %}

3
LukasM
9 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to create a User Activation Report?Knowledge Drop

Last tested: Nov 28, 2019 The ProblemTo look at three KPIs,1. Active Users (Activity within last 30 days)2. Retained Active Users3. Re-Activated Users (Activity within last 30 days, but no activity from 30 - 60 days, but activity from 90 days +)A SolutionIn order to do this, we will need some date manipulation and some windows functions, meaning we will use a derived table.Here is an idea of how to create this, using Standard SQL (Bigquery) Required data is User ID connected to an Activity Date (basically, what date they performed an activity on.) We will create a derived table like below, only needing to change the parts in [] with your data set. WITH daily_use AS (SELECT[MEMBER_ACCOUNT_ID] as user_id, cast(TIMESTAMP_TRUNC(timestamp_millis(TIMESTAMP_IN_HOUR),day) as date) as activity_dateFROM [DATABASE.TABLE]GROUP BY 1, 2)SELECTdaily_use.user_id, wd.date as date, CASE WHEN MIN( DATE_DIFF(wd.date, daily_use.activity_date, day) ) < 30 THEN "yes"ELSE "no" END as within_30, CASE WHEN M

130
Pieter_DeVries
Looker Staff
11 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

Can I use whitespace in filters?Knowledge Drop

Last tested: Mar 3, 2020 Starting in 7.4, YES!Say you have this table with a combination of leading and trailing spaces:In the past, Looker would trim all trailing and leading spaces from filter values, even if those spaces carried meaning for the customer's dataset. Now, you can include whitespaces in filter values without Looker trimming them!Here are some examples:Matches advanced works too! The important thing to remember is the ^ character is needed to escape the whitespace character(s), like:Please NOTE: With great power comes great responsibility. Since Looker no longer trims trailing and leading whitespaces, it's possible to get unexpected results if you accidentally include a whitespace in your filter value. This content is subject to limited support.                  

1020
Pieter_DeVries
Looker Staff
11 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
posted in コラム

表計算 (Table Calc) の窓関数 (Windows function)　を使う方法

みなさんこんにちは！表計算による窓関数は非常に便利なものでぜひ記事をアップしたいと思いました。下記の画像をぜひご覧ください。極端な例になりますが、およそ300行ほどのデータを２つの行に圧縮することができました。男子・女子の合計売り上げこちらのメソッドは割とシンプル、しかし魔法的と言えるでしょう。 まずはGroup集計（窓関数）かけたいコラムを指定し、（今回はProducts Department）値がはじまる行と次の値がはじまる行を変数にする表計算を作ります→ 表計算１名前：group_start_row:コード：match(${products.department}, ${products.department})そして→ 表計算２名前：next_group_start_row:コード：count(${products.department}) - match(${products.department}, offset(${products.department}, count(${products.department}) - row()*2 + 1)) + 2上記のデータ変数があれば、様々な窓関数が開放されます。例えば：名前：group_count:コード：${next_group_start_row} - ${group_start_row}又は、上の画像で使っている名前：group_sum:コード：sum(offset_list(${inventory_items.total_cost}, -1 * (row() - ${group_start_row}), ${next_group_start_row} - ${group_start_row})) 他にはRunning_totalの窓関数も 名前：group_running_total:コード：sum(offset_list(${inventory_items.total_cost}, -1 * (row() - ${group_start_row}), row() - ${group_start_row} + 1)) 上記の方法で簡単な集計が容易くなりますが、MAX()とか、複数の数字を比べるような器用な関数はどうしたらいいのか？となった時は今度はListの関数を使う出番です。例えば、ミッションは

0
Pieter_DeVries
Looker Staff
7 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to generate LookML for all datasets for BigQuery connectionKnowledge Drop

Last tested: Apr 23, 2020 The ProblemLooker only generates one view which is specified in connection setting as dataset even we select "All tables".  A SolutionListing all datasets ( comma-separated) in schema section in create project. This content is subject to limited support.                  

220
Pieter_DeVries
Looker Staff
11 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

ERROR : Internal database configuration issueKnowledge Drop

Last tested: Nov 11, 2020 Starting in 7.20, Looker now validates the dialect and version of the internal database during startup. If something other than the default in-memory Hypersql or a Mysql v5.7.x database is used as the internal database, Looker logs an error message before performing any startup migrations. There are two possible error messages:"The Looker internal database is not the default in-memory HyperSQL database or a MySQL 5.7.x database" This is thrown when you are using a supported dialect (i.e. hypersql by default, or mysql via looker-db.yml) but not a supported version (ex: mysql v8.0.1). "{dialect name} is not a valid dialect option for the Looker internal database" This is thrown when you aren't using a supported dialect in looker-db.yml (ex: aurora, mariadb, etc.). Because Looker is developed and tested around hypersql and mysql v5.7.x databases, those are the only two supported configurations. Given our lack of testing beyond hypersql and mysql v5.7.x, any dev

470
Pieter_DeVries
Looker Staff
11 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

Can I change value label alignment (vertical & horizontal) in stacked bar/column charts?Knowledge Drop

Last tested: May 18, 2020 No, that is currently not possible at the moment.If that is something you're interested in, please share your use case with Looker Product team via Pendo feedback. This content is subject to limited support.                  

910
Pieter_DeVries
Looker Staff
11 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to extract names from email address in table calcKnowledge Drop

Last tested: May 7, 2020 The ProblemUsers will sometimes want to get a substring of their dimension, for example, extracting first.last from first.last@gmail.comA SolutionWe can pretty easily do this with a table calc. We can either use 2 columns or combine them.Create a "position" column to get the length of the string before the value we want. For emails this will be @.Position: position(${users.email}, "@") Then, we create the substring table calc;Name: substring(${users.email},0,${position}-1) The above 2 steps will extract the names, if you would like to combine those into a single column it would look like:Name: substring(${users.email},0, position(${users.email}, "@")-1) This content is subject to limited support.                   

1770
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to create a First and Last date using Custom FieldsKnowledge Drop

Last tested: Jan 8, 2020 The ProblemSay you want to see a user's first and last query dates. This could be pretty difficult because it is in system_activity so you can't edit the lookml, and you can't easily min/max dates.A SolutionWe will use a combination of Custom Dimensions, Custom Measure and Table Calcs to achieve this.Here is a picture of what that looks like.Verify that you have Custom Fields enabled. Open an explore that has the data you are looking for. Set up the explore to have your Main Dimension (in this case we are using User ID). Everything else will be custom. Create the custom dimension, this will create a number that shows the difference in days between the date we are interested in and today.diff_days(${history.created_date},now()) Create the Custom Measures. 1. Day_diff_1 (this is the last_day) 2. Day_diff_2 (this is the first day) Same as above, just change measure type from min to max. Create the table calcs that show the dates. 1. Last_dateadd_days(${day_diff_1}

4210
Pieter_DeVries
Looker Staff
11 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
asked in Looker API

Copy dashboard from one folder to another and turn looks into look-less tiles automatically

This code is a bit of a work in progress, as I have it set to work with only a single look at the moment. Having said that, the logic works, so we would just need to work with the syntaxing.#Install Looker! pip install looker_sdk#Import packagesimport urllib3import looker_sdkimport jsonfrom looker_sdk import models, error#setup environmentos.environ['LOOKERSDK_API_VERSION']='3.1'os.environ['LOOKERSDK_BASE_URL']='XXXXX'os.environ['LOOKERSDK_CLIENT_ID'] = 'XXXXX'os.environ['LOOKERSDK_CLIENT_SECRET']='XXXXX'urllib3.disable_warnings()sdk = looker_sdk.init31()#Grab the query id of the Look/s in the original dashboarddashboard = sdk.dashboard( dashboard_id="1")query_id = dashboard["dashboard_elements"][0]["look"]["query"]["id"]#Create a new dashboard in the folder you want tocreate_dashboard = sdk.create_dashboard( body=models.WriteDashboard( title="api_test", folder_id="8" ))#Grab the id of the newly created dashboardnew_dashboard = sdk.search_dashboards( title="ap

0
Pieter_DeVries
Looker Staff
10 months ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
posted in Technical Tips & Tricks

Creating a pie chart with over 50 rows using table calcs to create an "Other" bucket

It is sometimes the case that we need to create a pie chart, or any other chart that has row limits, but our raw data exceeds those limits.We can always make Lookml or SQL Database changes to create buckets, such as seen here:but that doesn’t always match our work flow.My solution is an explore-only purely table calc driven solution. There are 3 main parts.Creating a new dimension with “Others”, notice it is important to sort on your measure to make sure “others” matches the criteria:  states_with_others: if(row()<50,${events.state},"Others") Creating a hide column, that allows us to use yes,no to hide certain rows so that they stay within our requirements: hide_after_50: if(row()>50,no,yes) This uses the “Hide No’s from Visualization” solution seen here: https://docs.looker.com/exploring-data/using-table-calculations#using_table_calculations_in_visualizations Integrating non-”other” dimensions with “other” dimensions: count_with_others: if(${states_with_others}="Others",sum(if($

0
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

How to hide percentiles (bottom 10% etc.)Knowledge Drop

Last tested: Mar 26, 2020 The ProblemHow can I hide the top 10% of my measures? It can seem simple, but it can be completed very easily with the right table calcs.A SolutionWe we create three main table calcs (they can be grouped into a single table calc if you like as well)First create the measure that you will be using. We will create the percentage of the whole. Basically, what against the entire columns value, what percentage is this row's value${view_name.field_name}/sum(${view_name.field_name}) We will define what percentile we want to cut. For this example, we will be cutting the bottom 10% so,percentile(${percentage_of_whole},0.1) Finally, we will ask a simple question,if(${percentage_of_whole}>${bot_10_percentile},yes,no)Then we will hide the no's and voila. The no row will disappear. If you want to create this all in one column its,if(${view_name.field_name}/sum(${view_name.field_name})>percentile(${view_name.field_name}/sum(${view_name.field_name}),0.1),yes,no) This co

2150
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
posted in Technical Tips & Tricks

Using multiple values in a single parameter

  In the event that you want to do something crazy like this:When you want parameters to be all they can be Parameters are incredibly useful tools, which basically work by injecting values into your sql or labels or wherever you put them. They are usually marred by the fact that you can only use a single value at a time per parameter field.However where we have SQL, there is a always a way to make things more complicated than they need to be, and more hacky than is feasible.Behind the method to the madness (Written in SNOWFLAKE): parameter: number_list { suggest_dimension: user_id type: string } dimension: secondary_dimension { type: string sql: array_construct( TO_NUMBER(SPLIT_PART({% parameter number_list %}, ',', 1)), TO_NUMBER( IFF( SPLIT_PART({% parameter number_list %}, ',', 2) = '', 0, SPLIT_PART({% parameter number_list %}, ',', 2))) ) ;; } dimension: change_value { type: string sql:

0
Pieter_DeVries
Looker Staff
1 year ago
Pieter_DeVries
Looker Staff
Pieter_DeVriesLooker Staff
published in Technical Tips & Tricks

String-type User Attributes and % WildcardsKnowledge Drop

Last tested: May 11, 2020 Starting in Looker 7.8 (and backport patches for 7.4 and 7.6), "string" type User Attributes will no longer support the "%" wildcard character in group or user values. Any "%" characters in a "string" type User Attribute's group or user values will be treated as regular, non wildcard characters. Note, this may impact the results of queries using such User Attributes in filters (specifically access_filters), as the "%" character will go from "matches anything" to "equals '%'". If "%" wildcard characters are needed in string-based User Attributes, please use the "string (advanced)" type as our docs recommend. This content is subject to limited support.                  

3400
Pieter_DeVries
Looker Staff
11 months 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