Pivot Table
  • 02 Aug 2022
  • 3 Minutes to read
  • Contributors
  • Dark
    Light

Pivot Table

  • Dark
    Light

Overview

A Pivot Table is table of grouped values that aggregates the individual items of a more extensive table within one or more discrete categories.

In this walkthrough, we will create a pivot table that conveys Netflix program ratings by country, and grouped by TV show or movie.

Reference Content

The following articles may be useful resources as you build your chart:

  • Creating a Chart: A walkthrough of the overall process of chart selection and creation.
  • Using Preset Explore: How to work with the Explore page, the primary interface used when creating a chart.

Step 1: Create New Chart

To create a bar chart, in the Toolbar, select + Chart.

The Create a new chart screen appears.

In the Choose a Dataset field, select a dataset to build your query from. In this case, we are selecting a dataset that includes Netflix program data.

By default, the #Popular category is selected — from here, you can select a Pivot Table in the chart gallery. Alternatively, enter pivot in the search field.

Next, select Create New Chart.

Pivot_Table_Select_Visualization_Type


Step 2: Configure the Chart

Time Panel

Reference: How to Configure the Time

The first step is to define the Time Column field. This field contains a time-related column from the dataset. More than likely, this field is pre-populated for you.

You can recognize time columns by looking in the Columns listing in the Metrics & Columns panel and finding those with a small clock icon.

In this example, there is only one time column available, so there is no need to make any changes to the Time Column field.

Some charts will include a Time Grain field, which enables you to define a new time granularity, such as data based on the day, minute, month, year, and so on. In this example, we will use the Day time grain.

Next, let's configure the Time Range field.

This field is used to specify a date & time range from which data will be extracted for the query. Generally-speaking, if you're working with a small dataset size, then it is best to use the No filter option (i.e., use all available data). If the dataset size is larger, though, then a date & time range is more appropriate.

In this case, we will not filter by time, so we'll select No filter.

Our completed Time panel looks as follows:

Pivot_Table_Time_Panel

Query Panel

For the table, we will use Country to define rows, and Type & Rating to define the columns. We'll use our only available metric, COUNT, and apply that metric to the rows.

Let's start by dragging & dropping the Country column to the Rows field, then drag & drop the Type and Ratings columns to the Columns field.

In the Metric field, drag & drop the COUNT metric and we'll apply that metric on the rows.

Pivot_Table_Configure

Next, let's configure the filter. We will filter by four countries: Canada, India, United Kingdom, and the United States.

Drag & drop the Country column to the Filters field.

A sub-menu appears. By default, the region operator is "in" which works perfectly for our requirements. In the text field, select Canada, India, United Kingdom, and the United States. If desired, you can enter a search term and then select the matching option.

Pivot_Table_Filters

In the Row Limit field, select 1000 — if you don't see "1000" as an option, then enter the value 1000 and then select in the drop-down.

This field is used to limit the number of columns that appear in the chart — if this number is too small or too large, then the pivot table may not be useful. So be sure to experiment with some values to find the Row Limit that works best for you.

Also, in the Pivot Options panel, complete as follows:

Pivot_Table_Options

The completed configuration for this walkthrough looks like this:

Pivot_Table_Configured_1


Step 3: Run the Query

Reference: The Content Panel

Select Run or, in the Content panel, Run Query.

Run_Query

The pivot table appears in the Content panel.

Complete_Pivot_Table

You can also expand the Data section to view data results and samples from the query.


Step 4: Finalizing the Chart

If you're happy with the visualization, be sure to give it a name:

Enter_Chart_Title_Here

If you'd like to customize your visualization, select the Customize tab.

Lastly, be sure to save your chart. Remember that you can also save your chart to a dashboard—or create a brand new dashboard—directly from the Save menu!

Here's a look at our finalized pivot table:

Complete_Pivot_Table_2


Was this article helpful?

What's Next