Difference between revisions of "Pivot"
Gadiyedwab (talk | contribs) |
Gadiyedwab (talk | contribs) |
||
| Line 3: | Line 3: | ||
==What's a Pivot?== | ==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 | + | 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. |
| + | |||
| + | [[File:pivot_product_sales_cateogory_year.png]] | ||
In this view, we summarize data by three category fields: | In this view, we summarize data by three category fields: | ||
| Line 15: | Line 17: | ||
==Why is it called a Pivot?== | ==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 look at an example. | + | 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 look at an 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. |
| + | |||
| + | [[File:pivot_product_sales_cateogory_year2.png]] | ||
==The Structure of a Pivot View== | ==The Structure of a Pivot View== | ||
| Line 24: | Line 28: | ||
#'''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. | #'''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. | #'''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: | The three areas are depicted in the following: | ||
| + | |||
| + | [[File:pivot_row_area.png]] | ||
| + | |||
| + | [[File:pivot_column_area.png]] | ||
| + | |||
| + | [[File:pivot_value_area.png]] | ||
| + | |||
==Terminology== | ==Terminology== | ||
| Line 30: | Line 42: | ||
*'''Row Labels/Row Field''' - A data field that provides category labels to be listed in the row area of the pivot (down the left side) | *'''Row Labels/Row Field''' - A data field that provides category labels to be listed in the row area of the pivot (down the left side) | ||
*'''Column Labels/Column Field''' - A data field that provides categories to be listed in the column area of the pivot (across the top) | *'''Column Labels/Column Field''' - A data field that provides categories to be listed in the column area of the pivot (across the top) | ||
| − | *'''Values/Value Field''' - A data field that is aggregated (counted, summed, averaged, etc.). These aggregated values are displayed in the value area. | + | *'''Values/Value Field''' - 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. |
| − | |||
| − | 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== | ==Selecting Fields== | ||
To select fields for the row, column, or value area use the "Choose Fields" button have the "Pivot Fields" dialog displayed in the sidebar if it's not shown already. | To select fields for the row, column, or value area use the "Choose Fields" button have the "Pivot Fields" dialog displayed in the sidebar if it's not shown already. | ||
| + | |||
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. | 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. | 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. | ||
| Line 43: | Line 55: | ||
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: | 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: | ||
| − | + | ||
| + | [[File:pivot_sales_month_year.png]] | ||
| + | |||
In this example, it's easy to compare sales in a particular month to sales in the same month in other years. | In this example, it's easy to compare sales in a particular month to sales in the same month in other years. | ||
| Line 49: | Line 63: | ||
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). | 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. | Depending on the field and the area you'll have various options, including changing the field label. | ||
| + | |||
| + | [[File:pivot_edit_settings.png]] | ||
===Selecting Date Parts=== | ===Selecting Date Parts=== | ||
When you drag a date/time field to the row or column area, it turns into "Year" field. To select "Quarter" and "Month" and to change the labels use the Field Settings dialog. | When you drag a date/time field to the row or column area, it turns into "Year" field. To select "Quarter" and "Month" and to change the labels use the Field Settings dialog. | ||
| + | |||
| + | [[File:pivot_date_setting.png]] | ||
==Subtotals== | ==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. If it's the inner most field, then it does not repeat and subtotaling will be pointless as we'll always subtotal a single value. | You can add or remove subtotals for a row or column field provided that it's not the inner-most row or column field. If it's the inner most field, then it does not repeat and subtotaling will be pointless as we'll always subtotal 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. | You can add or remove a subtotal using a right click (or touch) on the corresponding column heading and making the appropriate selection. | ||
| + | |||
| + | [[File:pivot_subtotal.png]] | ||
==Grand Totals== | ==Grand Totals== | ||
You can add or remove grand totals using a right click (or touch) on a column heading and making the appropriate selection. | You can add or remove grand totals using a right click (or touch) on a column heading and making the appropriate selection. | ||
| + | |||
| + | [[File:pivot_grand_total.png]] | ||
==Sorting== | ==Sorting== | ||
The pivot is naturally sorted to achieve its grouping. It's first sorted by the row areas field categories starting with the first row area field, and then sorted by the column area field categories. 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. | The pivot is naturally sorted to achieve its grouping. It's first sorted by the row areas field categories starting with the first row area field, and then sorted by the column area field categories. 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. | ||
| + | |||
| + | [[File:pivot_sort_direction.png]] | ||
===Sorting by Value=== | ===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. | 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. | ||
| + | |||
| + | [[File:pivot_sort.png]] | ||
A few important things to point out: | A few important things to point out: | ||
| Line 77: | Line 105: | ||
*When we sort by value, we sort based on the values of a particular column, in our example, "2011". | *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. | *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" | + | *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". |
| − | + | ||
| + | [[File:pivot_sort_in.png]] | ||
| + | |||
==Aggregating Values== | ==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. | 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. | 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. | 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: | Aggregate calculations (also called aggregate functions) are listed and explained in the following bullet points: | ||
| Line 98: | Line 131: | ||
To select the aggregate calculation for a value field, select a value field and use the button to display the "Field Settings" dialog. | To select the aggregate calculation for a value field, select a value field and use the button to display the "Field Settings" dialog. | ||
| + | |||
| + | [[File:pivot_calculation.png]] | ||
==Additional Calculations: "Display As"== | ==Additional Calculations: "Display As"== | ||
| Line 104: | Line 139: | ||
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. | 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. | ||
| + | |||
| + | [[File:pivot_display_as.png]] | ||
"Display As" is calculated for every cell for every value fields as described in the list below. | "Display As" is calculated for every cell for every value fields as described in the list below. | ||
| Line 129: | Line 166: | ||
To drill through, right click, (or touch), a cell and then select one of the following kinds of drill-through. | To drill through, right 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 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 row''' - displays the records that contribute to all the values in the row of the cell. | ||
Revision as of 12:14, 13 December 2012
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 look at an 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:
Terminology
- Row Labels/Row Field - A data field that provides category labels to be listed in the row area of the pivot (down the left side)
- Column Labels/Column Field - A data field that provides categories to be listed in the column area of the pivot (across the top)
- Values/Value Field - 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 have the "Pivot Fields" dialog displayed in the sidebar if it's not shown already.
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 "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. If it's the inner most field, then it does not repeat and subtotaling will be pointless as we'll always subtotal 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 categories starting with the first row area field, and then sorted by the column area field categories. 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 calculations listed above, you can apply additional calculations such as running totals or % difference. The aggregate calculation is performed first. It is generally performed by the data source. The "Display As" calculation is 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
- % 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
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, right 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.
The Drill-Through View
When you drill through to details, you see a list view showing the data records that make up the data in the cell, column, or row that you drilled through to details on.
The view title has the word "details" added at the end and it is shown at the end of the breadcrumbs. To go back to the pivot view, use the breadcrumb (the one before the last.)
The drill-through view is created temporarily. It gets overwritten the next time you drill through on a pivot or chart. If you want to save it you can do one of the following:
- Add it to favorites. You'll be prompted to rename it and convert it to a regular list view.
- Make a copy. You'll be prompted to give it a name and create a copy of the view. The view will become a regular view and be listed under "My Views".
- Share the view. This will convert the view to a regular view and share it with your team.
You can choose fields for your drill-through view, for example to see additional fields. Remember however that this view is temporary. You can choose fields for future drill-through views by modifying the list view of your pivot view. Let me explain. Every view in Explore Analytics has all 3 types: List, Pivot, and Chart. When you switch your pivot view to a list view using the "List" button, you can see the fields that will be shown when you drill through. Changing them will affect any future drill-through view. When you're done, use the "Pivot" button to switch your view back to pivot. You'll see that your pivot was not changed.
