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.
Contents
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:
- 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.
- 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.
- 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.
ServiceNow grouping by week
For a ServiceNow data source, the choice of the first day of the week, Sunday or Monday, is controlled by a ServiceNow system property - "glide.ui.date_format.first_day_of_week" and by default it considers the week as beginning on Monday. This ServiceNow property can be set to "1" to indicate Sunday as the first day of the week. It means that in most cases, it will group based on the ServiceNow property, not based on your selection here.
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".
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.
For full description on the Display As functionality, see Working with Values.
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.
Drill Down
A drill down action updates the view by adding a level of details. A "focus and drill down" action, filters data down to the selected category and replaces the selected field with the next field in the drill-down hierarchy. This is useful for understanding the current value by further breaking it down.
Drill down is available when a drill-down hierarchy is defined for this field. See the page Drill Down for details about setting up drill down.
