.png "Community Manager"){kind=icon}
Pivot tables are no stranger to the world of BI. In this article, we make some recommendations and key considerations for when you build your next pivot tables.
What you should know before reading this article
Applies to
This article applies to on-premise versions 4.9 and higher as well as cloud versions of Incorta.
Let's Go
Pivot or Transpose?
While there is an apparent insight type of pivot, there is another way to flip a table so rows become columns and columns become rows. This can be done by going to the cog (settings) in the analyzer in the upper right. Within the settings, there is a toggle to transpose the table.
We recommend developing the insight as a pivot table before leveraging the 'transpose' capability. Due to the setting's hidden nature, building a pivot table from the outset will help other insight developers quickly key in on how the insight was constructed.
Make a detailed view available
Because pivot tables are typically a form of an aggregated table, it is paramount to provide your insight consumer with access to the underlying detail data. If a user sees an unexpected value, give the user the ability to drill into the data and confirm or deny their suspicion.
Handle/Cleanse Duplicate Data
As with aggregated tables, ensure that data is cleansed and will provide accurate aggregation. For example, something as simple as casing differences ('Product A' vs. 'Product a') between the same dimension value can cause data to group into different rows.
Handle Empty Values
The default behavior of Incorta is to fill null rows with blank space. In many cases, you might seek to impute a value of 0. To do so, go to the analyzer settings, scroll to the 'Advanced' section and enter a 'Missing Text Value.'
Totals and Subtotals
Enhance the legibility of your report using totals or subtotals.
Totals: In the analyzer insight settings (top-right cog), select whether you would like to add a grand total column or a grand total row. You also have options to right-align or left-align totals. Traditional preference dictates that totals sit on the right side or bottom of the pivot chart.
Subtotals: In the row pill properties, toggle on the subtotals settings. In the pictures below, subtotals for the 'Quarter' pill are toggled on!
Before
After
Sort
By default, pivot tables will sort by the dimension in the grouping tray. As a result, the groupings default to being sorted alphabetically. Consider sorting your pivot table on the measure of performance.
Before (Defaulted to sorting by Category):
After (Sorted by Linetotal):
Merge Rows
By default, the merge rows configuration should be toggled on. Good! If not, go to the insight settings (top right cog) and toggle 'merge rows' on.
Before
After
