# Building visualizations

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

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2FzvUkc32e5jkRn6Bk7YWD%2Fpivot.mp4?alt=media&token=4e803219-16b3-47f3-9f5a-71dd211ecd7b>" %}

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

{% hint style="info" %}
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).
{% endhint %}

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:

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2FyKns3HovjNTNR5cEnBrc%2Ftable%20calc.mp4?alt=media&token=47cabb25-8e2d-4088-a666-16a24c986754>" fullWidth="true" %}

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:

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2FOVyr7v0EcctOUpXl2zkm%2Fcustom%20table%20calc.mp4?alt=media&token=2f06f9a2-e724-442b-ac8b-2716e58415f2>" fullWidth="true" %}

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](https://cloud.google.com/looker/docs/functions-and-operators) that will be helpful for table calculations. Below is a list of commonly used functions.

<details>

<summary>Commonly used table calculation functions</summary>

<table><thead><tr><th width="118.6754150390625">Function</th><th>Syntax</th><th>Purpose</th></tr></thead><tbody><tr><td>if</td><td>if(yesno_expression, value_if_yes, value_if_no)</td><td>If <code>yesno_expression</code> evaluates to <code>Yes</code>, returns the <code>value_if_yes</code> value. Otherwise, returns the <code>value_if_no</code> value</td></tr><tr><td>contains</td><td>contains(string, search_string)</td><td>Returns <code>Yes</code> if <code>string</code> contains <code>search_string</code>, and <code>No</code> otherwise. The <code>contains</code> function is case-sensitive.</td></tr><tr><td>now</td><td>now()</td><td>Returns the current date and time.</td></tr><tr><td>is_null</td><td>is_null(value)</td><td>Returns <code>Yes</code> if <code>value</code> is <code>null</code>, and <code>No</code> otherwise.</td></tr><tr><td>index</td><td>index(expression, n)</td><td>Returns the value of the <code>n</code>th element of the column created by <code>expression</code>, unless <code>expression</code> defines a column of lists, in which case returns the <code>n</code>th element of each list.</td></tr><tr><td>offset</td><td>offset(column, row_offset)</td><td>Returns the value of row <code>(n + row_offset)</code> in <code>column</code>, where <code>n</code> is the current row number.</td></tr><tr><td>pivot_offset</td><td>pivot_offset(pivot_expression, col_offset)</td><td>Returns the value of the <code>pivot_expression</code> in position <code>(n + col_offset)</code>, where <code>n</code> is the current pivot column position. Returns null for unpivoted results.For examples using <code>pivot_offset</code>, see the <a href="https://community.looker.com/explores-36/creating-a-running-total-across-rows-with-table-calculations-1375">Creating a running total across rows with table calculations</a> Community post and the <a href="https://cloud.google.com/looker/docs/best-practices/how-to-calculate-percent-change-percent-previous">Calculating Percent of Previous and Percent Change with Table Calculations</a> Best Practices page.</td></tr><tr><td><a href="https://cloud.google.com/looker/docs/pivot_index">pivot_index</a></td><td>pivot_index(expression, pivot_index)</td><td>Evaluates <code>expression</code> in the context of the pivot column at position <code>pivot_index</code> (1 for first pivot, 2 second pivot, etc.). Returns null for unpivoted results.For examples using <code>pivot_index</code>, see the <a href="https://community.looker.com/explores-36/using-pivot-index-in-table-calculations-3-28-1291">Using pivot_index in table calculations</a> and <a href="https://community.looker.com/explores-36/creating-a-percent-of-total-across-rows-with-table-calculations-1057">Creating a percent of total across rows with table calculations</a> Community posts.</td></tr><tr><td><a href="https://cloud.google.com/looker/docs/pivot_where">pivot_where</a></td><td>pivot_where(select_expression, expression)</td><td>Returns the value of <code>expression</code> for the pivot column that uniquely satisfies <code>select_expression</code> or <code>null</code> if such a unique column does not exist.</td></tr></tbody></table>

</details>

### Adding totals

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

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2F2gcMyPpjtW6OVPIhwugh%2Ftotals.mp4?alt=media&token=eb46b2ea-0792-4b78-9a2a-934ad424abc6>" %}

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

{% hint style="info" %}
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).
{% endhint %}

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2FX0RagqHuQYW41QeqATeg%2Fgrouping%20dimensions.mp4?alt=media&token=61c49073-5bb6-4c0a-97fc-55cc9504d3a3>" fullWidth="true" %}

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

{% hint style="info" %}
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).
{% endhint %}

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2FZtf333YxttNoO0DvgpLL%2Ffiltered%20measure.mp4?alt=media&token=e180811a-8945-475a-aed2-73bfc23d22b0>" fullWidth="true" %}

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

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2Fup4HZUCM3YZmsnDcECPi%2Fpivot%202.mp4?alt=media&token=1daabdea-42e8-47ee-8ff0-c0af5d0aa768>" fullWidth="true" %}

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

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2Fh8nO7hpyV9kBKF9GQhyZ%2Fstacked%20bar%20chart.mp4?alt=media&token=3b32af1a-82ff-4ad8-aeea-2315aabb72dc>" fullWidth="true" %}

### 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).

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2F9Sy20ysW42GhCdda0ebV%2F2025-05-07_12-17-33.mp4?alt=media&token=8449810f-49b6-43ce-88ee-4bd495ef4b8c>" fullWidth="true" %}

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

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2FYRV9xQ8xZcOluXxkp0Ze%2Fhiding%20column.mp4?alt=media&token=9212b374-13ec-4c6e-8984-2234756cca65>" fullWidth="true" %}

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

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2FsGZMyMEH3jsvSQ3uhJie%2Fcombo%20chart.mp4?alt=media&token=399c7841-3614-4fe8-87b1-c71008db7544>" fullWidth="true" %}

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

{% embed url="<https://files.gitbook.com/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FWDvO31PP3F01d6jisBTt%2Fuploads%2FOrEGJ8oKWGPHY8LYMGgX%2Fsecond%20y%20axis.mp4?alt=media&token=a8f1e0d1-f8b3-4076-890e-60959c295d67>" fullWidth="true" %}