Building visualizations

This page will help you with some of the basics of how to prep data and configure your visualizations in Looker.

Prepping your data

Here are some useful tools for getting the data to display exactly as you want before fine-tuning the visualization.

Pivoting data

If you want a dimension to populate into columns instead of rows, you can do so easily using the dimension pivot feature.

This is also useful if you want each value for the dimension to be broken out as a separate series in your chart.

Creating table calculations

Think of table calculations as a way to perform Excel-like functions on the data returned in a query. They are ad-hoc fields that allows you to display the output of a calculation involving one or more measures that already exist in your table.

Table calculations are ad-hoc dimensions, meaning they'll be saved in any visualization you create, but they won't be available by default in the explore. If you find yourself repeatedly adding a table calculation to a visualization, you should build the calculation in your project's LookML (or work with our agency to get it built for you).

Looker provides shortcuts for doing some of the most common table calculations:

  • % of column

  • % of previous row

  • % change from previous row

  • Rank of column

  • Running column total

Here's how to use those shortcuts:

However, if you have more specific needs for your table calculation, you can create one from scratch, like this example showing how to perform a calculation using two numbers in the chart:

In the editor, start typing the name of the field in your query that you want included in the table calculation, and then choose it when it appears in the list.

Looker documentation includes a full list of functions and operators that will be helpful for table calculations. Below is a list of commonly used functions.

Commonly used table calculation functions
Function
Syntax
Purpose

if

if(yesno_expression, value_if_yes, value_if_no)

If yesno_expression evaluates to Yes, returns the value_if_yes value. Otherwise, returns the value_if_no value

contains

contains(string, search_string)

Returns Yes if string contains search_string, and No otherwise. The contains function is case-sensitive.

now

now()

Returns the current date and time.

is_null

is_null(value)

Returns Yes if value is null, and No otherwise.

index

index(expression, n)

Returns the value of the nth element of the column created by expression, unless expression defines a column of lists, in which case returns the nth element of each list.

offset

offset(column, row_offset)

Returns the value of row (n + row_offset) in column, where n is the current row number.

pivot_offset

pivot_offset(pivot_expression, col_offset)

Returns the value of the pivot_expression in position (n + col_offset), where n is the current pivot column position. Returns null for unpivoted results.For examples using pivot_offset, see the Creating a running total across rows with table calculations Community post and the Calculating Percent of Previous and Percent Change with Table Calculations Best Practices page.

pivot_index(expression, pivot_index)

Evaluates expression in the context of the pivot column at position pivot_index (1 for first pivot, 2 second pivot, etc.). Returns null for unpivoted results.For examples using pivot_index, see the Using pivot_index in table calculations and Creating a percent of total across rows with table calculations Community posts.

pivot_where(select_expression, expression)

Returns the value of expression for the pivot column that uniquely satisfies select_expression or null if such a unique column does not exist.

Adding totals

Looker makes it easy to include row and column totals in the data:

Creating a new grouped dimension

If you want to roll up values from an existing dimension into larger groups, you can create a new grouped dimension. This acts like a normal IF statement in Excel or Google Sheets, where you can define an output and the input rules, along with a fallback value.

This is also helpful if you want to simply rename the output of a dimension in a visualization without having to touch your database.

Grouped dimensions created this way are ad-hoc dimensions, meaning they'll be saved in any visualization you create, but they won't be available by default in the explore. If you find yourself repeatedly adding a grouped dimensions to a visualization, you should build the dimension in your project's LookML (or work with our agency to get it built for you).

Creating a new filtered measure

If you need to apply a filter to a single measure (rather than all dimensions and measures) you can create a filtered measure on the fly. For example, you could use a filtered measure if you want to show a count of all orders next to a count of new orders.

Filtered measures created this way are ad-hoc dimensions, meaning they'll be saved in any visualization you create, but they won't be available by default in the explore. If you find yourself repeatedly adding a filtered measure to a visualization, you should build the filtered measure in your project's LookML (or work with our agency to get it built for you).

Configuring your visualization

Here are some common use cases for data visualizations along with instructions on how to accomplish them.

Break out dimension values into individual series

If you want each value returned in a dimension to be broken out into its own series, you should Pivot the dimension:

Stacked bar charts

To create a stacked bar chart, you must:

  1. Have a dimension pivoted (see the previous section for details on that)

  2. Switch to a column chart visualization

  3. Choose "Stacked" series positioning in the visualization editor

Comparison tiles

To create a tile with a big number and a comparison number:

  1. Add the measure you want for the big number and the measure you want for the comparison measure. The big number will be the left-most number, and the comparison measure will be the next measure. If you add more than 2 measures, it will always only show the two left-most measures.

  2. Choose the Single Value visualization

  3. In the visualization editor, go to the Comparison tab and toggle on "Show Comparison"

  4. Make adjustments to the options in the Comparison tab until it displays correctly

Note: If you need more than 2 measures in the Data pane to create a calculation for your visualization, you can add them and then hide the columns that you don't want displayed (see next section for details).

Hiding columns

There are some cases where you may need to include a field in the data table but not want it included in your visualization. For example, if you want to visualize only a table calculation but not the other fields that are used in the calculation. In this case, you can hide the columns that you don't want displayed in your chart or table.

To hide the field:

  1. Click on the gear icon for that column in the data pane

  2. Choose "Hide this field from visualization"

Combo charts

If you want to have a combination of columns and lines in your chart:

  1. Choose either the column or line visualization type

  2. In the visualization editor, go to the series tab

  3. Expand the series you want to switch to a column or a line, and change the type

Multiple Y axes

If you want to create two Y axes so that they are scaled independently:

  1. Open the visualization enter and go to the Y tab

  2. Drag the measure you want plotted onto a second Y axis to the Right Axes section

Was this helpful?