• Beta
Pivot Table
  • 15 Nov 2020
  • 3 Minutes To Read
  • Contributors
  • Print
  • Share
  • Dark
    Light

Pivot Table

  • Print
  • Share
  • Dark
    Light

Overview

In this topic we're going to talk about how to create a Pivot Table using Preset. A Pivot Table is used to reorganize table data using grouping, filtering, and sorting to present data in a more meaningful way. Row data can be transformed into column data and vice-versa.

In this walkthrough, we will create a Pivot Table based on publicly accessible data from the Covid-19 Coronavirus pandemic. Have a look at the Sample Data section on Connecting to Preset to download this data.

Goal

In the instructions below, we will create a Pivot Table that displays confirmed cases and deaths across 5 Latin American countries: Argentina, Chile, Brazil, Bolivia, and Paraguay. Data will be displayed by date for the last quarter.


Step One: Getting Started

To start, log on to Preset and then select a workspace.

Select_workspace

Next, let's define your datasource and select a chart type

At the top of the screen, select New. Then, in the drop-down menu, select Chart.

New_chart

After you do this, the Create a new chart screen appears.


Step Two: Select a Datasource and Visualization

In the Choose a datasource field, select a data source.

Note:

Please see Connecting to Preset to learn how to add a datasource to Preset. Be sure to check out the Sample Data section to add Covid-19 data.

Next, in the Choose a visualization type field, click the Table (default) icon.

Select_datasource

The Select a visualization type panel appears.

Look for the Pivot Table type—or use the Search feature to filter by the word “Pivot”—and then select.

After you've done this, go ahead and select the Create new chart button.

Select_Pivot_Table_1b


Step Three: Start Building your Chart

Now we're going to define the parameters of your new chart. To start, in the Datasource & Chart Type area, make sure that the Datasource and Visualization Type match what you just selected.

You can change either or both of these at any time!

Datasource_Pivot_Table

For the Pivot Table, the Time fields are used to select the time data we will use (Time Column) and the time period from which data will be included (Time Range).

  • In this example, we will use the date_trunc option in the Time Column.
  • For the Time Grain, we will use Week.
  • Lastly, for the Time Range, we will look at data from the Last Quarter.

Here's what it looks like:

Time_Pivot_Table

Next, let's have a look at the Query section.

In the Metrics field, we will select #Deaths and #Confirmed to indicate Total Deaths and Total Confirmed Cases, respectively.

In the Filters field, navigate to Country, select the Operator "in," and then select the countries Argentina, Chile, Brazil, Bolivia, and Paraguay.

The process looks like this:

Query_Filter_Pivot_Table

In the Group by field, we will group data by date — so select the date_trunc option.

Lastly, in the Columns field, we will create 5 columns for each country — so select country.

The Query panel in this example would look as follows:

Query_Pivot_Table(1)

Before we run the query, though, we need to define some pivot options. Let's have a look at the available options in the Pivot Options panel:

Pivot_Options_Panel_1

  • Aggregation function: Indicates what kind of values should be displayed. In this case, we will display the sum.
  • Show totals: If checked, an additional column is created that displays the totals of all countries. We left this unchecked.
  • Number format: Indicate the formatting of values. In this case, we are displaying 2 decimal places with a "k" at the end to denote thousands.
  • Combine Metrics: If selected, shows the breakdown of metrics for each column by grouping. This is selected.
  • Transpose Pivot: Flips the rows and columns — not selected here.

Step Four: Visualize the Chart

In the Content Panel, select Run Query.

RQ_Pivot_Table_1b

Was This Article Helpful?