This page demonstrates how to add and customize a LookML dashboard element of type: looker_waterfall
with LookML dashboard parameters in a dashboard.lkml
file.
For information about building a waterfall chart through the Looker UI, see the Waterfall chart options documentation page.
Example usage
An N
indicates that a numeric value is required. Single quotation marks indicate descriptive text and should not be included in live code.
## BASIC PARAMETERS
name: element_name
title: 'Element Title'
type: looker_waterfall
height: N
width: N
top: N
left: N
row: N
col: N
refresh: N (seconds | minutes | hours | days)
note_state: collapsed | expanded
note_display: above | below | hover
note_text: 'note text'
## QUERY PARAMETERS
model: model_name
explore: explore_name
fields: [view_name.field_name, view_name.field_name, …]
dimensions: [view_name.field_name, view_name.field_name, …]
measures: [view_name.field_name, view_name.field_name, …]
sorts: [view_name.field_name asc | desc, view_name.field_name, …]
fill_fields: [view_name.field_name, view_name.field_name, …]
limit: N
column_limit: N
filters:
view_name.field_name: 'Looker filter expression' | 'filter value'
filter_expression: 'Looker custom filter expression'
listen:
dashboard_filter_name: view_name.field_name
hidden_fields: [view_name.field_name, view_name.field_name, …]
query_timezone: 'specific timezone' | user_timezone
merged_queries:
- 'primary query definition'
- 'next source query definition'
join_fields:
- field_name: view_name.field_name
source_field_name: view_name.field_name
## SERIES PARAMETERS
up_color: "hex string" | 'CSS named color string'
down_color: "hex string" | 'CSS named color string'
total_color: "hex string" | 'CSS named color string'
color_application:
collection_id: 'collection ID'
palette_id: 'palette ID'
options:
reverse: true | false
## VALUE PARAMETERS
show_value_labels: true | false
label_color: ['color value']
## X-AXIS PARAMETERS
x_axis_scale: auto | ordinal | time
show_x_axis_label: true | false
show_x_axis_ticks: true | false
x_axis_zoom: true | false
## Y-AXIS PARAMETERS
show_y_axis_labels: true | false
show_y_axis_ticks: true | false
y_axis_gridlines: true | false
y_axis_zoom: true | false
Parameter definitions
Parameter Name | Description | |
---|---|---|
Basic Parameters | ||
name (for elements) |
Creates the element | |
title (for elements) |
Changes the way the element name appears to users | |
type (for elements) |
Determines the type of visualization to be used in the element | |
height (for elements) |
Defines the height of the element in units of tile_size for layout: tile and layout: static dashboards |
|
width (for elements) |
Defines the width of the element in units of tile_size for layout: tile and layout: static dashboards |
|
top |
Defines the top-to-bottom position of the element in units of tile_size for layout: static dashboards |
|
left |
Defines the left-to-right position of the element in units of tile_size for layout: static dashboards |
|
row |
Defines the top-to-bottom position of an element in units of rows for layout: newspaper dashboards |
|
col |
Defines the left-to-right position of an element in units of columns for layout: newspaper dashboards |
|
refresh (for elements) |
Sets the interval at which the element will automatically refresh | |
note_state |
Defines whether the note will be collapsed or expanded if it is too big to fit on a single row within the element's width | |
note_display |
Defines where the note is displayed on the element | |
note_text |
Specifies the text displayed in the note | |
Query Parameters | ||
model |
Defines the model to be used for the element's query | |
explore (for elements) |
Defines the Explore to be used for the element's query | |
fields |
Defines the fields to be used for the element's query. This can be used in place of dimensions and measures . |
|
dimensions |
Defines the dimensions to be used for the element's query | |
measures |
Defines the measures to be used for the element's query | |
sorts |
Defines the sorts to be used for the element's query | |
fill_fields |
Defines the dimensions that utilize the dimension fill option | |
limit |
Defines the row limit to be used for the element's query | |
filters (for elements) |
Defines the filters that cannot be changed for the element's query | |
filter_expression |
Defines a custom filter that cannot be changed for the element's query | |
listen |
Defines the filters that can be changed for the element's query, if filters (for dashboard) have been created |
|
hidden_fields |
Specifies any fields to use in the query but hide from the chart | |
query_timezone |
Defines the time zone that should be used when the query is run | |
merged_queries |
Defines a merged results query | |
Series Parameters | ||
up_color |
Sets the color of positive value bars in the chart | |
down_color |
Sets the color of negative value bars in the chart | |
total_color |
Sets the color of the total value bar in the chart | |
color_application |
Applies a color collection and palette to the chart | |
Value Parameters | ||
show_value_labels |
Shows labels for each data bar | |
label_color |
Specifies a color value for labels | |
X-Axis Parameters | ||
x_axis_scale |
Specifies how the x-axis scale is calculated | |
show_x_axis_label |
Shows or hides the x-axis label | |
show_x_axis_ticks |
Shows values on the x-axis | |
x_axis_zoom |
Specifies whether to allow zooming along the x-axis | |
Y-Axis Parameters | ||
show_y_axis_labels |
Shows or hides the y-axis label | |
show_y_axis_ticks |
Shows values on the y-axis | |
y_axis_gridlines |
Extends gridlines from the y-axis | |
y_axis_zoom |
Specifies whether to allow zooming along the y-axis. Disabled if x_axis_zoom: false . |
Basic parameters
When defining a LookML dashboard element, you must specify values for at least the name
and type
basic parameters. Other basic parameters, such as title
, height
, and width
, affect the appearance and position of the element on a dashboard.
name
This section refers to the
name
parameter that is part of a dashboard element.
name
can also be used as part of a dashboard filter, described on the Dashboard parameters documentation page.
Each name
declaration creates a new dashboard element and assigns it a name. Element names must be unique. Names are sometimes referenced in the elements
parameter when you're using layout: grid
dashboards.
- name: orders_by_date
title
This section refers to the
title
parameter that is part of a dashboard element.
title
can also be used as part of a dashboard, described on the Dashboard parameters documentation page.
title
can also be used as part of a dashboard filter, described on the Dashboard parameters documentation page.
The title
parameter lets you change how an element's name will appear to users. If unspecified, the title defaults to the element name
.
Consider this example:
- name: sales_overview
title: '1) Sales Overview'
If you used this format, instead of the element appearing as Sales Overview, it would appear as 1) Sales Overview.
type
This section refers to the
type
parameter that is part of a dashboard element.
type
can also be used as part of a dashboard filter, described on the Dashboard parameters documentation page.
type
can also be used as part of a join, described on thetype
(for joins) parameter documentation page.
type
can also be used as part of a dimension, described on the Dimension, filter, and parameter types documentation page.
type
can also be used as part of a measure, described on the Measure types documentation page.
The type
parameter determines the type of visualization to be used in the element.
- name: element_name
type: text | looker_grid | table | single_value | looker_single_record |
looker_column | looker_bar | looker_scatter | looker_line | looker_area |
looker_pie | looker_donut_multiples | looker_funnel | looker_timeline |
looker_map | looker_google_map | looker_geo_coordinates | looker_geo_choropleth | looker_waterfall | looker_wordcloud | looker_boxplot
See the type
(for LookML dashboards) documentation page for an overview of the different types of LookML dashboard elements.
height
This section refers to the
height
parameter that is part of a dashboard element.
height
can also be used as part of a dashboard row, described on the Dashboard parameters documentation page.
For dashboards with tile
or static
layouts
The height
parameter defines the height of an element, in units of tile_size
(which is defined in pixels), for layout: tile
and layout: static
dashboards.
For example, the following code specifies tile_size: 100
and height: 4
, making the orders_by_date
element 400 pixels in height.
- dashboard: sales_overview
tile_size: 100
...
elements:
- name: orders_by_date
height: 4
...
For dashboards with newspaper
layout
The height
parameter defines the height of an element, in units of row, for layout: newspaper
dashboards.
A dashboard with newspaper layout defaults to an element height of 6 rows, or about 300 pixels. The minimum height is 1 row for dashboards with a preferred viewer
parameter set to dashboards-next
. The minimum height is 2 rows for dashboards with a preferred viewer
parameter set to dashboards
.
For example, the following code sets an element to be 12 rows tall, or twice as tall as other elements that are set to the default:
- dashboard: sales_overview
layout: newspaper
...
elements:
- name: orders_by_date
height: 12
...
width
This section refers to the
width
parameter that is part of a dashboard element.
width
can also be used as part of a dashboard, described on the Dashboard parameters documentation page.
The width
parameter defines the width of an element, in units of tile_size
, for layout: tile
and layout: static
dashboards.
For example, the following code specifies tile_size: 100
and width: 4
, making the orders_by_date
element 400 pixels in width.
- dashboard: sales_overview
tile_size: 100
...
elements:
- name: orders_by_date
width: 4
...
The width
parameter defines the width of an element, in units of columns, for layout: newspaper
dashboards.
A dashboard with newspaper layout defaults to a width of 24 columns.
For example, the following code sets the element to half the width of the dashboard:
- dashboard: sales_overview
layout: newspaper
...
elements:
- name: orders_by_date
width: 12
...
top
The top
parameter defines the top-to-bottom position of an element, in units of tile_size
, for layout: static
dashboards.
For example, the following code specifies tile_size: 100
and top: 4
, positioning the top edge of the orders_by_date
element 400 pixels from the top of the dashboard.
- dashboard: sales_overview
tile_size: 100
...
elements:
- name: orders_by_date
top: 4
...
left
The left
parameter defines the left-to-right position of an element, in units of tile_size
, for layout: static
dashboards.
For example, the following code specifies tile_size: 100
and left: 4
, positioning the left edge of the orders_by_date
element 400 pixels from the left side of the dashboard.
- dashboard: sales_overview
tile_size: 100
...
elements:
- name: orders_by_date
left: 4
...
row
For layout: newspaper
dashboards, the row
parameter defines the row that the top edge of an element is placed on.
A dashboard begins with row 0 at the top of the dashboard. A dashboard with newspaper layout defaults to an element height of 6 rows, meaning the dashboard elements at the top of a dashboard (row: 0
) would default to taking up rows 0-5.
Each row is 50 pixels tall, which means the default element height of 6 rows is 300 pixels.
For example, the following code sets an element to be set on the second row of elements in the dashboard, assuming elements are set at the default height:
- dashboard: sales_overview
layout: newspaper
...
elements:
- name: orders_by_date
row: 6
...
col
For layout: newspaper
dashboards, the col
parameter defines the column that the left edge of the element is placed on.
Dashboards are divided into 24 columns. A dashboard begins with column 0 at the left of the dashboard. A dashboard with newspaper layout defaults to an element width of 8 columns, meaning the dashboard elements at the left of a dashboard (col: 0
) would default to taking up columns 0-7.
For example, the following code sets an element to be set in the third column of elements in the dashboard:
- dashboard: sales_overview
layout: newspaper
...
elements:
- name: orders_by_date
col: 16
...
refresh
This section refers to the
refresh
parameter that is part of a dashboard element.
refresh
can also be used as part of a dashboard, described on the Dashboard parameters documentation page.
The refresh
parameter allows an element to reload automatically on some periodic basis, thereby retrieving fresh data. This is often helpful in settings where a dashboard is constantly displayed, such as on an office TV. Note that the dashboard must be open in a browser window for this parameter to have an effect. This setting does not run in the background to "pre-warm" the dashboard cache.
The refresh rate can be any number (without decimals) of seconds, minutes, hours, or days. For example:
- name: orders_by_date
refresh: 2 hours
Use caution when setting short refresh intervals. If the query behind the element is resource-intensive, certain elements may strain your database more than desired.
note_state
The note_state
parameter defines whether a note will be collapsed or expanded if it is too big to fit on a single row within the element's width. If you choose collapsed
and the note is too long, the note will end in a clickable ellipsis (...
) that can be used to read the full note. If you choose expanded
and the note is long, the note will run onto additional lines.
note_display
The note_display
parameter defines where a note is displayed on an element. above
places the note at the top of an element, below
places it at the bottom of an element, and hover
requires the user to hover their mouse over a ?
icon to see the note.
note_text
The note_text
parameter specifies the text displayed in an element note.
Query parameters
When defining a LookML dashboard element, you must specify values for at least the model
and explore
query parameters, and at least one field must be specified using the dimensions
parameter, the measures
parameter, or the fields
parameter. You can also use the other query parameters described below to control the way data is displayed in a dashboard element.
model
The model
parameter defines the model to use for the element query. If unspecified, it will default to the model where the dashboard resides.
- name: orders_by_date
model: ecommerce
The model
parameter accepts LookML constants. You can define a constant in the manifest file for your project, then use the syntax "@{constant_name}"
to set the constant as the value for model
. Using a constant lets you define the name of a model in one place, which is particularly useful if you're updating the name of a model that is used by multiple dashboard elements.
For more information and an example of using constants with LookML dashboards, see the constant
parameter documentation page.
explore
This section refers to the
explore
parameter that is part of a dashboard element.
explore
can also be used as part of a model, described on theexplore
parameter documentation page.
explore
can also be used as part of a dashboard filter, described on the Dashboard parameters documentation page.
The explore
parameter defines the Explore to use for the element query.
- name: orders_by_date
explore: order
The explore
parameter accepts LookML constants. You can define a constant in the manifest file for your project, then use the syntax "@{constant_name}"
to set the constant as the value for explore
. Using a constant lets you define the name of an Explore in one place, which is particularly useful if you're updating the name of an Explore that is used by multiple dashboard elements.
For more information and an example of using constants with LookML dashboards, see the constant
parameter documentation page.
fields
The fields
parameter defines the fields to use for the element query. Use the syntax view_name.dimension_name
to specify the fields.
## single field example
- name: orders_by_date
fields: order.order_date
## multiple fields example
- name: orders_by_date
fields: [order.order_date, order.order_count]
If you use the fields
parameter, you do not need to use the dimensions
and measures
parameters.
dimensions
The dimensions
parameter defines the dimension or dimensions to use for the element query. Use the syntax view_name.dimension_name
to specify the dimension. Don't include dimensions
if the query doesn't have any.
## single dimension example
- name: orders_by_date
dimensions: order.order_date
## multiple dimension example
- name: orders_by_date
dimensions: [order.order_date, customer.name]
measures
The measures
parameter defines the measure or measures to use for the element query. Use the syntax view_name.measure_name
to specify the measure. Don't include measures
if the query doesn't have any.
## single measure example
- name: orders_by_date
measures: order.count
## multiple measure example
- name: orders_by_date
measures: [order.count, order_item.count]
sorts
The sorts
parameter defines the sorts to be used for the element query. The primary sort is listed first, then the secondary sort, and so on. Use the syntax view_name.field_name
to specify the dimension or measure. Don't include sorts
if you want to use Looker's default sort order. Descending sorts are suffixed with desc
; ascending sorts don't need a suffix.
## single sort example
- name: orders_by_date
sorts: order.order_date desc
## multiple sort example
- name: orders_by_date
sorts: [order.order_date desc, customer.name]
fill_fields
The fill_fields
parameter defines the dimensions that utilize the dimension fill option. Use the syntax view_name.dimension_name
to specify the dimensions.
- name: orders_by_date
fill_fields: [orders.created_date, orders.shipped_date]
limit
The limit
parameter defines the row limit that should be used for the element query. The limit applies to the number of rows before any pivots are applied.
- name: orders_by_date
limit: 100
filters
This section refers to the
filters
parameter that is part of a dashboard element.
filters
can also be used as part of a dashboard, described on the Dashboard parameters documentation page.
filters
can also be used as part of a measure, described on thefilters
parameter documentation page.
The filters
parameter defines the non-changeable filters that should be used for the element's query. If you would like filters that a user can change in the dashboard, you should set up the filters using filters
for dashboards, then apply them to the elements using listen
.
The syntax for filters
is:
- name: element_name
filters:
orders.created_date: 2020/01/10 for 3 days
orders.status: Shipped
# You can create multiple filter statements
Each filter can accept a Looker filter expression or a value constant. You can also use the _localization
or _user_attributes
Liquid variables in the filter expression for flexible filter values.
filter_expression
The filter_expression
parameter defines a non-changeable custom filter for the element's query. If you would like filters that a user can change in the dashboard, you should set up the filters using filters
for dashboards, then apply them to the elements using listen
.
- name: element_name
filter_expression:
- diff_days(${users.created_date},${user_order_facts.first_order_date}) > 60
The Looker filter expressions documentation page lists the Looker filter expressions.
listen
Dashboards can have filters that allow users to change the data behind dashboard elements. These are set up by using the filters
for dashboards parameter. For a filter to impact an element, the element must be set up to "listen" for that filter, using the listen
parameter.
The syntax for listen
is:
- name: element_name
listen:
filter_name_goes_here: dimension or measure on which to apply
the filter using view_name.field_name syntax
# You can add more than one listen statement
For example, you might create a filter called Date that requires a user to enter a date into the filter field in the UI. You could then apply the value that the user enters to the orders_by_date
element like this:
- dashboard: sales_overview
...
filters:
- name: date
type: date_filter
elements:
- name: orders_by_date
listen:
date: order.order_date
...
This concept is described in more detail on the Building LookML dashboards documentation page.
hidden_fields
The hidden_fields
parameter indicates which fields, if any, are used in the query but hidden in the chart. Any hidden fields will appear in the data table section of an Explore.
hidden_fields: [inventory_items.count, distribution_centers.id]
query_timezone
The query_timezone
parameter specifies the time zone in which the query will be run. The time zone options are shown on the Values for timezone
documentation page. If you want the query to run using the viewer's time zone, you can assign the value as user_timezone
.
- name: orders_by_date
query_timezone: America/Los Angeles
- name: orders_by_customer
query_timezone: user_timezone
merged_queries
The merged_queries
parameter defines how to merge results from several queries. You define the component queries under the merged_queries
parameter and use the join_fields
subparameter to define the merge rules.
The syntax for merged_queries
is:
merged_queries:
- 'primary query definition'
- 'next source query definition'
join_fields:
- field_name: 'field from the source query using the view_name.field_name syntax'
source_field_name: 'joined field from the primary query using the
view_name.field_name syntax'
# more join_fields definitions as needed
- 'next source query definition'
join_fields:
- field_name: view_name.field_name
source_field_name: view_name.field_name
# more join_fields definitions as needed
# additional source queries and join_fields definitions as needed
The first query defined under merged_results
is the primary query, and the other queries will be merged with that one.
For example, by joining on the stock symbol, company name, and Facebook username, you can merge two queries to return results for company name, company Facebook username, IPO year, stock symbol, number of employees, and job count:
- name: merged_results_element
title: Merged Results Tile
merged_queries:
- model: market_research
explore: company_info
fields: [company.name, company.facebook_username, ipo.public_year, ipo.stock_symbol]
filters:
company.facebook_username: "-NULL"
ipo.valuation_amount: NOT NULL
sorts: [ipo.public_year desc]
- model: crunchbase
explore: companies
fields: [companies.name, ipo.stock_symbol, companies.facebook_username,
companies.number_of_employees, jobs.job_count]
filters:
companies.number_of_employees: NOT NULL
ipo.stock_symbol: "-NULL"
companies.facebook_username: "-NULL"
sorts: [jobs.job_count desc]
join_fields:
- field_name: ipo.stock_symbol
source_field_name: ipo.stock_symbol
- field_name: companies.name
source_field_name: company.name
- field_name: companies.facebook_username
source_field_name: company.facebook_username
Then you can apply element parameters to style the visualization as you desire.
Series parameters
The parameters described in this section correspond to the options in the Series menu of the visualization editor for waterfall charts.
up_color
Set the color of the positive value bars in the chart. The color value can take a hex string enclosed in quotes, such as "#2ca6cd
", or a CSS named color string, such as mediumblue
.
up_color: blue
The up_color
parameter overrides the color_application
parameter, if it is used.
down_color
Set the color of the negative value bars in the chart. The color value can take a hex string enclosed in quotes, such as "#2ca6cd
", or a CSS named color string, such as mediumblue
.
down_color: "#ff0000"
The down_color
parameter overrides the color_application
parameter, if it is used.
total_color
Set the color of the total value bar in the chart. The color value can take a hex string enclosed in quotes, such as "#2ca6cd
", or a CSS named color string, such as mediumblue
.
total_color: yellow
The total_color
parameter overrides the color_application
parameter, if it is used.
color_application
The color_application
parameter, and its subparameters collection_id
and palette_id
, can be used to apply a specific color collection and palette to a dashboard element. For an overview of Looker's native color collections, see the Color collections documentation page.
If you have the collection ID and palette ID for the palette you want to use, you can enter those IDs into the collection_id
and palette_id
subparameters. A collection ID or a palette ID may be an alphanumeric code or be based on the name of the color collection. Alphanumeric codes are used for Looker's native collections. They are instance-specific and look like this:
color_application:
collection_id: 1297dk12-86a7-4xe0-8dfc-82de20b3806a
palette_id: 93c8aeb7-3f8a-4ca7-6fee-88c3617516a1
Custom color collections use collection and palette IDs based on the name of the color collection, which are portable across instances and look like this:
color_application:
collection_id: blue-tone-collection
palette_id: blue-tone-collection-categorical-0
You can also use the UI to find the colors, collections, or palettes that you want and generate the LookML to add them to your dashboard. Navigate to a piece of user-defined content (like a Look, a dashboard, or an Explore), and apply the colors, collection, or palette that you want to that content's visualization using the UI. Once you've done that, you can follow the steps to get dashboard LookML, copy the LookML that was produced, and paste it in the color_application
section.
color_application
and the Positive, Negative, and Total Value Bars
By default, the first color of the designated palette is applied to the positive value bars in the waterfall chart, the second color of the palette is applied to the negative bars of the waterfall chart, and the third color of the designated palette is applied to the total bar of the waterfall chart. The optional subparameter reverse
changes the application to the last color, second-to-last color, and third-to-last color, respectively.
color_application:
collection_id: blue-tone-collection
palette_id: blue-tone-collection-categorical-0
options:
reverse: true
The up_color
, down_color
, and total_color
parameters override any colors designated in the color_application
parameter, if both types of color parameters are used.
Value parameters
The parameters described in this section correspond to the options in the Values menu of the visualization editor for waterfall charts.
show_value_labels
Display the value of a bar, line, or point next to the data point.
show_value_labels: true | false
## default value: false
label_color
The label_color
parameter accepts a color value for the labels. The color value can be formatted as a hex string, such as #2ca6cd
, or as CSS named color string, such as mediumblue
.
label_color: [blue]
X-axis parameters
The parameters described in this section correspond to the options in the X menu of the visualization editor for waterfall charts.
x_axis_scale
This parameter determines how the x-axis scale is calculated.
auto
: The scale will be inferred from the underlying data. This is the default setting.ordinal
: The data will be plotted as evenly spaced, discrete entries.time
: The data will be plotted as time and the axis will be labeled appropriately. This will not work if the underlying data can't be converted to dates.
x_axis_scale: auto | ordinal | time
## default value: auto
show_x_axis_label
This parameter determines whether the x-axis label is shown.
show_x_axis_label: true | false
## default value: true
show_x_axis_ticks
This parameter determines whether values are shown on the x-axis.
show_x_axis_ticks: true | false
## default value: true
x_axis_zoom
This parameter specifies whether users can zoom into the x-axis of the visualization. When x_axis_zoom
is set to true
, zooming is available. When x_axis_zoom
is set to false
, zooming is not available.
If x_axis_zoom
is set to false
, y_axis_zoom
is disabled.
x_axis_zoom: true | false
# default value: true
Y-axis parameters
The parameters described in this section correspond to the options in the Y menu of the visualization editor for waterfall charts.
show_y_axis_labels
This parameter determines whether the y-axis label is shown.
show_y_axis_labels: true | false
## default value: true
show_y_axis_ticks
This parameter determines whether values are shown on the y-axis.
show_y_axis_ticks: true | false
## default value: true
y_axis_gridlines
This parameter determines whether gridlines are extended from the y-axis.
y_axis_gridlines: true | false
## default value: true
y_axis_zoom
This parameter specifies whether users can zoom into the y-axis of the visualization. When y_axis_zoom
is set to true
, zooming is available.
When y_axis_zoom
is set to false
, users cannot zoom into smaller portions of the y-axis. However, users may still be able to zoom into smaller portions of the x-axis if the x_axis_zoom
parameter is set to true
.
If x_axis_zoom
is set to false
, y_axis_zoom
is disabled.
y_axis_zoom: true | false
# default value: true