Pivot Table

Introduction

Feature(s)

A pivot table, also called an aggregation table, is a powerful tool to sort and aggregate data. Unlike other charts, pivot tables allow you to set multiple different dimensions, like row and column dimensions, and multiple measures.

Application Scenario(s)

The following is a sales pivot table. The sales can be aggregated to obtain the total sales of each order.

Basic Requirement(s)

To design a pivot table, you need to add at least one dimension and measure fields.

Setting Procedure

A dashboard is used for statistical analysis based on existing data. It allows you to aggregate and calculate data from various sources such as forms, the data factory, and aggregate tables. Note that you need to input data into these sources before the data analysis.

Adding a Chart for Statistics

Select a dashboard, click Stats, and select a form (such as Order Form) as the data source.

Selecting a Chart Type

Select Pivot Table under Chart Type.

Setting Row/Column Dimension(s)

Drag the fields you need to analyze, such as Order Number and Customer Name, from Fields to Dim.(Row)/ Dim.(Col). Then set Dim.(Row) and/or Dim.(Col).

Note:

At least one dimension should be set in a pivot table.

You can further set Display Name, Aggregation, Data Display Format, Sort, and others for the fields.

Select a dimension field, click , and select an item from the drop-down list.

Setting Measure(s)

A measure is a column with numerical data. It is used to measure target values. In one pivot table, you can add multiple measures.

For Measure, select fields under Fields, and then set them as measures, or add new calculated fields.

For example, the total amount is quite large and you want to display it in millions. To make it happen, add a calculated field, and add a formula to divide the total by 1,000,000. Then drag the field to Measure.

Setting Aggregation

Aggregation means the way you aggregate data.

1. Set the aggregation for dimension fields.

Choose a dimension field, and select Aggregation from the drop-down list.

For example, Date&Time fields can be aggregated by Year, Year-Quarter, Year-Month, Year-Week, and Year-Month-Day.

2. Set the aggregation for measure fields.

Choose a measure field, and select Aggregation from the drop-down list.

Then choose Sum, AVG, Max, Min, Count, or Dedupe from Aggregation. If the measure field is not a number field, you can only choose Count and Dedupe.

Setting Display Serial No.

Select a Dim.(Row) field whose serial numbers you want to display, and click Display Serial No..

For example, display the serial number of Customer Name, and display the total sales in an ascending order.

If it is unnecessary to display serial number, ignore this step.

Setting Aggregation Row

If you need to set an aggregation row, tick the items below to decide where the row will be displayed.

If not, untick it.

Setting Subtotal

Subtotal allows you to aggregate data for each dimension in a pivot table.

For example, in the sales scenario, subtotals can be used to aggregate sales revenue by regions and stores.

If there are two or more dimensions, multiple subtotals will appear on the table.

Note:

1. Subtotals are only supported for rows instead of columns now.

2. All the aggregation methods for Measure are available for Subtotal except Dedupe and AVG.

3. You cannot set Data Alert and Conditional Formatting for Subtotal.

4. Functions (including SUM, AVERAGE, COUNT, MAX, and MIN) are added for calculated fields. If you add a calculated field to Measure, subtotals for that field will not be calculated. Instead, they will be denoted with a hyphen (-).

Demonstration

Depending on the dimensions and measures, different aggregating methods brings different results.