- 10 Mar 2023
- 3 Minutes to read
-
Print
-
DarkLight
Pivot Table
- Updated on 10 Mar 2023
- 3 Minutes to read
-
Print
-
DarkLight
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.
- The Chart Builder Interface: How to work with the Chart Builder 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.
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:
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.
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.
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:
The completed configuration for this walkthrough looks like this:
Step 3: Run the Query
Reference: The Content Panel
Select Run or, in the Content panel, Run Query.
The pivot table appears in the Content panel.
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:
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: