As a good manager you periodically want to have a retrospective look at all of the observations reported in your organization. Pivot table provides you a flexible way to summarize and reorganize your data based on various properties such like observation category, place, status and many others.
Let's check an example use case, where you want to see the number of observations per each Category in each Place. In other words, you want to know what kind of incidents are happening across the places in your organization.
First, open the pivot grid page by clicking the Statistics tab and then the Pivot grid:
Creating a new data query
Once you are in the Pivot grid page there is no data to view yet as you need to load it first. Start by pressing the "New data query" to open a form where you will specify what kind of data you are interested in:
The opened form is pretty simple. It mainly consists of only three fields: rows, columns and aggregate.
The idea of a pivot table is that you line up properties of one type on the X-axis of a table as columns, and properties of another type on the Y-axis as rows, and get a corresponding aggregate value in the intersection between them.
In our example we choose "Category" for rows, "Place" for columns (or the other way around), and "Observation count" as an aggregate:
Once you press the Load button you will see a table similar to this one:
Here we can see that, for instance, in the place called "05182 Gibson Lakes" there were 12 "Counterfiet cash" incidents observed overall.
Editing the data query
We now see the total number of observations, what about yearly and monthly statistics? For that all we need to do is - slightly modify our data query by adding "Year" and "Month" to the columns field.
First, click the "Edit data query" button in order to open our data form:
Then add "Year" and "Month" to the columns field by selecting them, and then click the "Load" button:
Once the data is loaded, you are going to see a table similar to this:
Here we see that those previous 12 observations from "05182 Gibson Lakes" are now split into October, November and December months of year 2018.
Let's modify our rows and columns once more, so that we can also see who reported the observations and how many of them are open, closed or don't have any status at all.
In addition to that, this time we will filter our data so that we only see the "Counterfeit cash" category and the "05182 Gibson Lakes" place in our table.
Again, we click the "Edit data query" button, and then check "Category, Reporter name, Status" for rows, and "Place, Month" for columns:
If you noticed, we removed "Year" from the columns. That's because of one limitation in our pivot grid - we can't select more than 5 rows and columns in total. It is a measure to prevent loading too much data which the pivot table wouldn't be able to process.
We already know that we have data only for the year of 2018, so it doesn't hurt if we remove the "Year" column.
Next click the "Show filters" checkbox to reveal all the filters that we can use, and type "cou" into the field which says "Type to search for categories":
Once you start typing the matching option will come up, just select it by clicking on it (or if you are using a physical keyboard, you can also select it by pressing ⬇️ key and hitting Enter).
Similarly, search for the "gib" in the "Type to search for places" field to find and select the "05182 Gibson Lakes" place, and then press the "Load" button:
Finally, we should be able to see our filtered table with the reporter names and statuses:
Once you get a grip of using the rows and columns and filtering the data, make sure to also check our other short article, which shows how to sort pivot table, change the order of rows and columns, export table to Excel document and more! Here is the link: