In one of my previous posts (How to read data from Pivot Tables using VBA), I talked about using the getpivotdata() function in VBA to read values from a Pivot Table. In this article, we are going to look at building an alternative to pivot tables. Let’s begin.
Pivot tables are an immense help while working with data sets – they sum up the data into a small, neatly organized space. You can pick and choose the various values from the drop down and analyze the change in results by isolating or combining a few variables. You can also add, delete or move the fields to change the amount of data you want to see in the table.
Oftentimes a pivot will simply be used to group a large amount of data pertaining to a hierarchy – say for example a pivot which shows sales of various products in relation to the various divisions on the firm, geographical regions within those divisions, sales people working in those locations and so on and so forth. The task is simply to group and present the data along while keeping in mind the hierarchical relationships.
The easiest way to present this is to add and stack everything up in a hierarchy in the pivot table’s page field. For example, we may create something like this:
The user can select any item that he wishes to view and check out the results. However when used only to view information by using drop-downs from the various fields in this way, pivot tables suffer from a major handicap – the page field dropdowns do not follow hierarchical constraints. Take for example the user selecting “Division 1” from the list of divisions from the first page field in the pivot table. Then on clicking the second drop-down pertaining to the list of regions, the user will be presented with a list of ALL the regions of the firm, rather than only those in which that particular division (in our case “Division 1”) operates. Similarly, once the user chooses a particular region, say “Region 1”, the list of drop-downs for the counties under the region will show ALL counties rather than only those which are a part of that region.
Taking our example, if the user was well versed with how the firm is structured, then this can be considered a small glitch. However, what if the firm had a large number of divisions, regions, counties, area managers and sales executives? The user would have been confused and our attempt at presenting the information to him would have failed miserably. (Now this this beahvior of the pivot tables may be useful under some specific circumstances but does not help much when dealing with the situation described above)
The Pseudo Pivot Table – An alternative approach to pivot tables
An alternative approach is to presenting this information is to generate what I call pseudo pivot tables. They reduce / filter out the irrelevant fields and present only the relevant ones to the user. Here is an example of a pseudo pivot that can act as an alternate to pivot and avoid junk showing up on the page fields of the pivot table. (File stored at www.storewith.com)
There are three steps to creating the pseudo pivot tables:
1. Read data from pivot to filter out values
2. Clean the data so that only the relevant values are displayed
3. Present this clean data to the user using drop-downs generated using named lists
Read data from pivot
We first add a few rows before the pivot and add formulae to filter out unique values for each field (we use the row numbers to index these values). At this point, we also take care to ensure that the values displayed are related to what the user has selected (and not show ALL values).
Clean the data so that only the relevant values are displayed
Once we’ve separated out the values in the previous step, we sort them. We also convert the row numbers into the actual text values using the offset() function.
Present this clean data to the user using drop-downs generated using named lists
We create a named list for each of the drop-down fields. The named lists ensure that only non-blank values are displayed to the user. Once we have all the lists ready, we simply create a dummy pivot table and then use cell validation feature to create the drop-downs. We add getpivotdata() formula to fetch the values from the pivot table as per users selection. (Now since getpivotdata() works only when the arguments describe a single cell, we may need to create another pivot where the subtotals for all the fields have been turned on. We point the getpivotdata() formula to this second pivot rather than the first one to avoid the formula from erring out.)
(The above 2 steps will increase the file size.)
Since the user may choose values from one or more of the dropdowns, we would also need to create a formula that passes only those many criteria as arguments to the getpivotdata() formula. In short, the number of arguments provided to the getpivotdata() formula need to be dynamic and not fixed – they vary as per users input.
1. Shows only the relevant data to the user. Saves time and eliminates second guessing.
2. If used with a small or medium size data set, the response time may as fast as a corresponding pivot table.
1. Needs effort to set-up. (though I’ve tried to line up everything so that it can generally be extended to a larger pivot by simply inserting more columns and rows)
2. Increases the size of the file.
Having said that, the end result is a neat looking pseudo pivot. It mimics the functioning of a pivot table yet only displays only the relevant values from the pivot table and not all the values thus eliminating “pivot-junk”