0

Pivot Tables

Introduction

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

How to Create a Pivot Table

This article applies to on-premise versions 4.9 and higher as well as cloud versions of Incorta.

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.

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.

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.

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.'

 

 

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 

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):

 

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

 

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like Follow
  • 1 mth agoLast active
  • 42Views
  • 2 Following

Product Announcement

Incorta 2014.2 is now available!