Pivot

The pivot view is a powerful data analysis tool. This page describes the pivot view and how to use it to analyze your data.

What's a Pivot?

A pivot view is a table that shows summarized numerical data grouped by multiple categories. Let’s review an example of a pivot table showing Product Sales by Category and Year.

In this view, we summarize data by three category fields:

• Product Category – category labels are listed in the rows: "Garden” and "Home"
• Product Name – category labels are listed in the rows: "Gas grill", "Hammock", etc.
• Year – year labels are listed in the columns (across the top): "2008", "2009", through "2012"

The numerical values being summarized is the sales amount.

Why is it called a Pivot?

When you work with a pivot view, it’s easy to move categories from the rows (listed down the side on the left) to the columns (listed across the top) and vice-versa. You can think of it as a pivoting action centered on the top-left corner of the view. Let’s continue with our example. Let's move the "Product Category" to the columns (above the year). Now "Garden" and "Home" are listed left-to-right instead of top-down.

The Structure of a Pivot View

A pivot view consists of the following three areas:

1. Row Area – holds categories that are listed in the rows (down the side on the left). The row area can have multiple fields (two in our example). Each field provides its set of labels.
2. Column Area – holds categories listed in the columns (across the top). The column area can have multiple fields. In the previous example when we moved the product category to the column area we saw two fields in the column area.
3. Value Area – holds numeric amounts such as sums or counts. As we’ll see later, the value area can have multiple fields, allowing us to show multiple numeric calculations such as sums, counts, or averages.

The three areas are depicted in the following:

Row Area

Column Area

Value Area

Terminology

• Row Labels/Row Fields - A data field that provides category labels to be listed in the row area of the pivot
• Column Labels/Column Fields - A data field that provides categories to be listed in the column area of the pivot
• Values/Value Fields - A data field that is aggregated (counted, summed, averaged, etc.). These aggregated values are displayed in the value area. When a pivot view has more than one value field, we get another category that labels these fields. For example "Count" (count of sales) and "Sales" (sum of sales amount). These labels can go in the row area or the column area. A special field called "Values" allows you to place these labels in the row or column area. When a pivot view has just one value field, the special "Values" field is hidden as it is not applicable.

Selecting Fields

To select fields for the row, column, or value area use the "Choose Fields" button to display the "Pivot Fields" dialog in the sidebar.

In the Pivot Fields dialog, you select fields by dragging them from the fields list at the top to one of the areas below. You can also drag fields between and within areas to order them.

A field can be selected for the row or column areas only once (only one or the other). However, you can select a field to the value area multiple times. This is useful for showing different aggregates of the same fields.

Working with Dates

Dates are hierarchical in nature with months nested in quarters which themselves are nested in years. The pivot view allows you to group data by months, quarters, and years, show subtotals at every level and event put these parts of the data in different areas as we see in the next example:

In this example, it's easy to compare sales in a particular month to sales in the same month in other years.

Editing Field Settings

To edit settings for any field, first click (or touch) the field to highlight it, then use the "Edit Settings" button (the button with the pencil icon).

Depending on the field and the area you'll have various options, including changing the field label.

Selecting Date Parts

When you drag a date/time field to the row or column area, it turns into a "Year" field. To select "Quarter" and "Month" and to change the labels use the Field Settings dialog.

Subtotals

You can add or remove subtotals for a row or column field provided that it's not the inner-most row or column field. Subtotaling the inner-most field is unnecessary because there's always a single value.

You can add or remove a subtotal using a right-click (or touch) on the corresponding column heading and making the appropriate selection.

Grand Totals

You can add or remove grand totals using a right click (or touch) on a column heading and making the appropriate selection.

Sorting

The pivot is naturally sorted to achieve its grouping. It's first sorted by the row areas field in order and then sorted by the column area fields. Although this order should not be changes, you can still control the sort direction. For example, to list the year in descending order, select "Sort "Year" newest to oldest" from the column heading right-click (or touch) menu. That would list "2012", then "2011", etc.

Sorting by Value

It is often useful to depart from the normal sort and order the pivot rows based on the numeric values. For example, we can show the month with the greatest sales amount in the year 2011 at the top.

A few important things to point out:

• When we sort by value, we can only sort in columns (vertically) and not horizontally. If you want to sort the other way, you should first pivot the relevant categories to the rows and then sort vertically.
• When we sort by value, we sort based on the values of a particular column, in our example, "2011".
• To turn sort by value off and return to the normal sort, use the procedure in the "Sorting" section above.
• If there are no fields in the column area and there are multiple fields in the row area, then there are two options for sorting by value: You can sort the entire column (e.g., "Sort "Amount"), and you can sort in a particular category (except the inner-most category), for example you can sort within product category by selecting "Sort "Amount" ... in "Product Category".

Aggregating Values

You can choose any field for the value area. If the field is not numeric, then the only options available for aggregating are counts, as counts yield a numeric value.

For numeric fields you can also sum, average, and calculate the min and max values.

The calculation is performed for each cell based on the values of the field in the group of data records represented by this cell.

Aggregate calculations (also called aggregate functions) are listed and explained in the following bullet points:

• sum - A sum of the values. Empty (null) values are not added to the sum. If there are no items to sum, then the cell is empty. Therefore there's a difference between zero and empty. Zero means that we had some values that added up to zero. For example, in our previous examples, the sum for January 2008 is blank because there were no sales (0 data records) for January of 2008.
• count rows - A count of the data records. If there are no records, then the cell will be blank. A zero will never be shown. All rows are counted regardless of the value of the field.
• count distinct values - A count of the distinct (unique) values of the field. This option is not supported by all data sources. If you don't see this option, it means that your data source does not support this option.
• count non-empty values - A count of the data records that have a value in the field. For example, in a survey, you may have 50 participants, but only 48 of them provided an answer for a particular question. This option is not supported by all data sources. If you don't see this option, it means that your data source does not support this option.
• average - The average of the non-empty values. For example, if we have 50 records, but only 48 of the have a value, then the average is the sum divided by 48. When requesting subtotals and grand totals, the average shown is based on the total sum divided by the count of non-empty values. If count of non-empty values is not supported for your data source, then totaling averages may not be supported.
• min - The minimum numeric value in the data records. Null/empty values don't count.
• max - The maximum numeric value. Null/empty values don't count.

Selecting Aggregate Calculations

To select the aggregate calculation for a value field, select a value field and use the button to display the "Field Settings" dialog.

Additional Calculations: Display As

On top of the aggregate calculations listed above, you can apply additional calculations such as running totals. The aggregate calculations are performed first. The "Display As" calculations are performed on the results of the aggregate calculations.

In the description we mention "Base Field" and "Base Item". These are shown in the Field Settings dialog as "Field" and "Item" below the "Display values as" selection. To see these selections, click on the "Display As" button in the dialog.

"Display As" is calculated for every cell for every value fields as described in the list below.

• difference from - Shows the difference from the value of the Base Item in the Base Field.
• % difference from - Shows the percentage difference from the value of the Base Item in the Base Field
• % of - Shows values as a percentage of the value of the Base Item in the Base Field
• running total in column - Shows the values in each column as a running total
• running total in row - Shows the values in each row as a running total
• running total in - Shows successive items in the Base Field as a running total. For example, if the Base Field is "Month", then "running total in month" will run a total of January, February, and so on. It creates a running total for every combination of other row/column fields except "Month". For example, for every product and every year there's a running total in Month.
• running total by - Show items for the each item in the Base Field as a running total. For example to have a running total showing the balance of an account use "running total by account".
• % of row - Shows the values in each row as a percentage of the total for the row
• % of column - Shows the values in each column as a percentage of the total for the column
• % of total - Shows the value in each cell as a percentage of the total for all cells
• % of total by - Shows the value in each cell as a percentage of the total by Base Field
• % running total in column - Calculates running total in column, then display it as % of column
• % running total in row - Calculates running total in row, then display it as % of row
• % running total in - Calculates running total in Base Field, then display it as % of the total in Base Field
• % running total by - Calculates running total by Base Field, then display it as % of total by Base Field

Calculated Values and Calculated Columns

To create new pivot columns that display calculations based on data in the pivot, please see the page about Calculated Values and Calculated Columns.

Drill Through to Details

In a pivot table, every non-empty value-area cell represents a set of records. These records are selected by the labels of the column area shown directly above the cell (column headings) and the labels of the row area shows directly to the left of the cell (row labels).

A drill-through action displays a list of these records.

To drill through, click, (or touch), a cell and then select one of the following kinds of drill-through.

• Details on this cell - displays the records that contribute to the value of this cell.
• Details on this row - displays the records that contribute to all the values in the row of the cell.
• Details on this column - displays the records that contribute to all the values in the column of the cell.

For more information, see the page Pivot Drill Through to Details.

⇦ Working with Lists

⇧ Table of Contents

⇨ Calculated Values and Calculated Columns