Implementation
Using the example above, the software will find all distinct values for Region. In this case, they are: North, South, East, West. Furthermore, it will find all distinct values for Ship date. Based on the aggregation type, sum, it will summarize the fact, the quantities of Unit, and display them in a multidimensional chart. In the example above, the first datum is 66. This number was obtained by finding all records where both Region was East and Ship Date was 2005-01-31, and adding the Units of that collection of records (i.e., cells E2 to E7) together to get a final result.
Pivot tables are not created automatically. For example, in Microsoft Excel one must first select the entire data in the original table and then go to the Insert tab and select "Pivot Table" (or "Pivot Chart"). The user then has the option of either inserting the pivot table into an existing sheet or creating a new sheet to house the pivot table. A pivot table field list is provided to the user which lists all the column headers present in the data. For instance, if a table represents sales data of a company, it might include Date of sale, Sales person, Item sold, Color of item, Units sold, Per unit price, and Total price. This makes the data more readily accessible.
| Date of sale | Sales person | Item sold | Color of item | Units sold | Per unit price | Total price |
|---|---|---|---|---|---|---|
| 2013-10-01 | Jones | Notebook | Black | 8 | 25000 | 200000 |
| 2013-10-02 | Prince | Laptop | Red | 4 | 35000 | 140000 |
| 2013-10-03 | George | Mouse | Red | 6 | 850 | 5100 |
| 2013-10-04 | Larry | Notebook | White | 10 | 27000 | 270000 |
| 2013-10-05 | Jones | Mouse | Black | 4 | 700 | 2800 |
The fields that would be created will be visible on the right hand side of the worksheet. By default, the pivot table layout design will appear below this list.
Pivot Table fields are the building blocks of pivot tables. Each of the fields from the list can be dragged on to this layout, which has four options:
- Filters
- Columns
- Rows
- Values
Some uses of pivot tables are related to the analysis of questionnaires with optional responses but some implementations of pivot tables do not allow these use cases. For example the implementation in LibreOffice Calc since 2012 is not able to process empty cells. [6] [7]
Filters
Report filter is used to apply a filter to an entire table. For example, if the "Color of Item" field is dragged to this area, then the table constructed will have a report filter inserted above the table. This report filter will have drop-down options (Black, Red, and White in the example above). When an option is chosen from this drop-down list ("Black" in this example), then the table that would be visible will contain only the data from those rows that have the "Color of Item= Black".
Columns
Column labels are used to apply a filter to one or more columns that have to be shown in the pivot table. For instance if the "Salesperson" field is dragged to this area, then the table constructed will have values from the column "Sales Person", i.e., one will have a number of columns equal to the number of "Salesperson". There will also be one added column of Total. In the example above, this instruction will create five columns in the table — one for each salesperson, and Grand Total. There will be a filter above the data — column labels — from which one can select or deselect a particular salesperson for the pivot table.
This table will not have any numerical values as no numerical field is selected but when it is selected, the values will automatically get updated in the column of "Grand total".
Rows
Row labels are used to apply a filter to one or more rows that have to be shown in the pivot table. For instance, if the "Salesperson" field is dragged on this area then the other output table constructed will have values from the column "Salesperson", i.e., one will have a number of rows equal to the number of "Sales Person". There will also be one added row of "Grand Total". In the example above, this instruction will create five rows in the table — one for each salesperson, and Grand Total. There will be a filter above the data — row labels — from which one can select or deselect a particular salesperson for the Pivot table.
This table will not have any numerical values, as no numerical field is selected, but when it is selected, the values will automatically get updated in the Row of "Grand Total".
Values
This usually takes a field that has numerical values that can be used for different types of calculations. However, using text values would also not be wrong; instead of Sum, it will give a count. So, in the example above, if the "Units sold" field is dragged to this area along with the row label of "Salesperson", then the instruction will add a new column, "Sum of units sold", which will have values against each salesperson.
| Row labels | Sum of units sold |
|---|---|
| Jones | 12 |
| Prince | 4 |
| George | 6 |
| Larry | 10 |
| Grand total | 32 |
