Create a Pivot Data Source

Pivot Data Source

A Pivot data source allows you to extract meaningful data when working with a large dataset by summarizing and grouping the data. Pivot does not change the underlying data, instead, it simply reorganizes the data to reveal useful information.

Tip
It's important to plan out how you'd like to organize the data as it'll make it easier to create your pivot.

Steps to Create a Pivot Data Source 

1. Navigate to Analytics -> Data Sources.
2. Click on New Data Source button.
3. Select Pivot Type.
   
   
   
   
4. Name your data source. Overwrite the default data source name Untitled Data Source.
5. In the Input Source tab, click on Add Data Source to add the data source you wish to Pivot. Select your data source from the list. The columns from the original data source will be displayed in the Input tab. These columns cannot be edited. 
6. Next, click on the Pivot tab. This is where you will choose how to organize and summarize your data. In the Row and Column area, select the data source column that you'd like to slice your data. The Pivot table to the right of the screen will be generated based on the Row or Column, its grouping configuration, and Value(s) you define. 
   
   The example below shows a summary of the 2022 Actual YTD data grouped by two dimensions: Department and Budget Status.
   
   

 

Pivot Tab

Pivot Row/Column Grouping Types

The setup of the pivot row and column are similar. What differs is how they're used for display. There can only be at most one pivot row and at most one pivot column but at least one of them must be configured in order to produce the pivot table result.

The pivot row/column determines how values are grouped from the input data source. There are three types of grouping:

1. Distinct Values
   Distinct data values from the specified column will appear as row/column headers. This is the default grouping.
   
   
2. Date Aggregation
   This option is only available if the specified column is of type Date. Use this type to aggregate dates by Day, Week, Month, Quarter, Fiscal Quarter, Year, Fiscal Year.
   
   The date groups will be contiguous so missing dates will be filled in automatically if there are gaps between values from the input data source.
   
   
3. Custom Groups
   Using a set of conditions and labels, you can create a custom grouping in your pivot. It works similarly to the IFS() function. A use case for this type is when your data values do not contain the desired group name. You can use the Custom Group to generate the group name using a set of conditions and labels. 
   
   When setting up a pivot row using Custom Group, the group labels will appear in the first column. This can be very useful for charting. Setting up a pivot column using Custom Group means that the group labels will appear in the column headers. In other words, a pivot row grows the data source vertically while pivot columns grow the data source horizontally.
   
   Note: Custom groups will be displayed even if a group is empty (i.e. conditional formula returned false for everything)

Pivot options:

• Show Totals
  Use this option to display the grand totals. For a pivot row, the last row of the data source will display the total value. For a pivot column, there will be a Total column to the far right of the data source for each pivot value.
  
  
• Order
  Use this to sort the data in Ascending or Descending order.

Here's an example of a Pivot using Custom Groups. It shows how we can use Custom Groups to group the Budget (Col B) and Actual (Col C) columns based on the conditions:

If Actual (Col C) > Budget (Col B), then display the group name 'Over budget', else display the group name 'Below Budget'.

The original data source looks like this:

The Pivot data source looks like this:

Pivot Values

Pivot values are based on the column selected and it appears in the body of the pivot table. The grouped values are summarized based on the function you selected. You can define multiple pivot values. The functions available are Average, Minimum, Maximum, Sum, Count, Percentile. 

Filter Tab

Set filtering criteria in the Filter tab to narrow your display of rows.

Output Source Tab

In the Output Source tab, you can add an internal description for the data source, change the column data type, and formatting.  You can also add additional columns to the data source if required.