02.02 Consolidation, Splitting & Pivotting

Here is a video, or read below.

Consolidation

In the report above we have four dimensions in the down axis: Company, Division, Department and Account. This creates a great many rows of data. It is quite possible that we are not actually interested in looking at this data to this level of detail, as split by department for example – we might simply like to see our data consolidated to a company and division level.

If we remove dimensions from the down axis, which we can do in FastClose using the “Rows / Columns” dialog, we are said to be “Consolidating” the data. Consolidating, will not change the numbers in the totals at the bottom of the report but because we are no longer analysing by, in this case, “Department”, fewer rows are displayed and each row that is displayed, is now the sum of those that we had previously relating to the different departments. We can see something like this below:

If we consolidated again by removing Account ie: Company, Division & Account to Company & Division – we will get even fewer rows in our report, but still have the same totals.

Splitting

The reverse operation, adding dimensions to the report, is called “Splitting” and allows us to understand in greater detail the elements that make up a high level number. Again this is done using the “Rows / Columns” dialog which can be used to add new dimensions to either of the across or down axes. Adding a dimension to the down axis will add a new perspective to break down and analyse the numbers by, provide a greater level of detail and insight. Adding a dimension to the across axis does exactly the same thing but additionally as we discussed earlier, leads to the column multiplying effect.

Pivotting

Pivoting is an important but simple part of the report process. Sometimes the report you are working on, has the right information but it isn’t oriented the right way. The “Rows / Columns” dialog which we previously used to add or remove dimensions to the report, can also be used to pivot them between the “Across” and “Down” axes. An example is shown below, pivoting the company dimension from down to across: