Pivot tables are useful because they let you group your data together in different ways so you can more easily make comparisons. A pivot table can help quickly summarize the data and highlight the desired information.
The "pivot" part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table in order to view it from a different perspective. To be clear, you're not adding to, subtracting from, or otherwise changing your data when you make a pivot. Instead, you're simply reorganizing the data so you can extract useful information from it.
A pivot table usually consists of row, column and data (or fact) fields. These fields allow several kinds of aggregations including: sum, average, standard deviation, count etc.
Use Cases:
If you are still feeling a bit confused about what a pivot table actually does, don't worry. This is one of those technologies that's much easier to understand once you've seen it in action. Here are a couple of examples.
Note: Pivot tables will display a maximum of 500 rows. To display more rows, please use a Datagrid.
Interactive example:
Here is an example of how pivot table works inside mWater: Water Point Functionality. This example uses reported data on the current functional status of public water points. By using a pivot table, you can automatically aggregate the various types of water points by current status reported. Inside this example you can also see the data aggregated by length of lifetime. The columns and rows can all be customized and can automatically be calculated how you want your data to be summarized.
Video:
Here we create a basic pivot table to summarize population of communities by region.
Creating Pivot Tables
Now that you have a better sense of what pivot tables can be used for, let's get into the nitty-gritty of how to actually create one.
To create a pivot table you will need to create a new dashboard and use the Dashboard Builder Tools. To learn more about the features of dashboards and widgets please go to the Creating Dashboards Tutorial.
In edit mode:
Step 1: Insert a pivot table into your dashboard
Click and drag the "Pivot" widget and insert where you want it to show up.
To populate your data click to edit either in the middle of the new widget or in the top right corner
Pivot widget
Step 2: Select data and create a table
First thing is to select the source of your data. Inside mWater we have data on sites, surveys or specific indicators inside an advanced category.
Select source for data in pivot table
Step 3: Edit how the data is displayed in pivot table
After selecting the data source you will see areas to select the type of fields to populate the columns and rows.
These fields will contain the unique data points you want to aggregate in your pivot table.
Options of how you want data displayed inside pivot table
Step 4: Format how you want the values displayed
Each type of value will allow you to read your data in the intended format (currency, percentage, decimal points, or how its normally reported).
Format values for pivot table
Step 5: Fine tune your calculations
How the columns and rows show your data might not go deep enough into how you want the table to calculate each particular value. You can easily add another column or row to show the data in another format like average, percentage, maximum, or minimum depending on what you want to calculate.
To allow visualization to help you describe your data points you can add a custom color scheme.
Add a single column to display calculation of aggregate data
Calculation for single column in pivot table
Color scheme customization for visualization of data
Columns
Each column has a type Single or Multiple. For each type of column you can customize formatting; labels, style, and size of borders. Single columns are used for summary columns and empty columns. Multiple columns are used for disaggregate data by a particular field allowing you to filter out values from displaying in your pivot table. If you click inside the columns in edit mode you can add a unique calculation.
Inside our example Water Point Functionality. The column labeled "Percent functional" is a single column with a unique calculation that aggregates the data inside the table to produce a percentage. The column color scheme is customized to show low functional types by coloring them darker.
Rows
Each row has a type that similar to how columns are sorted. For each type of row you can customize formatting; labels, style, and size of borders. Single rows are used for summary rows and empty rows. Multiple rows are used to disaggregate data by a particular field allowing you to filter out values from displaying in your pivot table. If you click inside the rows in edit mode you can add a unique calculation.
Inside our example Water Point Functionality. The row labeled "Total" is a single row with a unique calculation that aggregates the data inside the table to produce a summary of the rows to produce a total for each type of water point. The rows for the data showing the length of lifetime and functionality have a unique calculation that divides the date it was installed with 365 days. This calculation allows for the comparison inside the pivot table.
Summation
Creating a summary of your data with a single column or row is important when you want to highlight the aggregation of the data source. Summation fields can be customized to show numerical values of your data in the format you want it displayed. Inside mWater the best way to create a summation field is by adding a single column to the left of the table or row at the top or bottom of the table.
Multiple data sources
Pivot tables have an advanced feature where you can bring together data from multiple, entirely different data sources as long as you can disaggregate them by the same dimension. So for example you can count the number of water point sites in one column split by rows by the year they were added, and then you can add a second column counting the number of school sites, also split by the year they were added. As another example, you can bring multiple indicators into the same table and organize them by a shared dimension such as a date, admin region, or project code for example.
To use this feature, first setup a pivot table with a single data source. Then add a row or column for which you start defining the calculation by clicking into it. At the bottom of the popup modal you'll see the option Advanced: Override Data Source. This is where you can select a different data source for this intersection. Once you have selected the data source, you must fill in the Fields to disaggregate data by section by pointing it to a field in your new data source that matches the disaggregation that's already active in the pivot table. So if you have a date field as the original disaggregation, you need to select a date field for disaggregating the second data source as well. Then set the calculation at the top of the modal as you normally would.
Advanced option to Override Data Source in pivot tables
