Pivot Table

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.

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.

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.

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

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.

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!

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:

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:

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:

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:

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