Pivot
Pivot Table distributes data for easy consumption. It spreads out the data in long, winding tables by converting categories into columns. Pivot can be created by selecting the Column, Row, and Data fields. Here's a quick video on how to use the Pivot transform.
To apply the pivot transform
1. Click the Transform menu and select the Pivot option. The Pivot pane will slide open to view.
Info: You can also click the 2. Select the fields that you want to convert to columns in the Columns section. Choose the fields to feature as rows in the Rows section and the fields with data in the Data section.
3. You can also select the Filters tab if you want to apply any filters. Using filters, you can selectively filter data based on the filter conditions applied over one or more columns. If you want to learn more about filters, click here .
4. Select the Sort tab if you want to rearrange the order of your columns and rows. The fields added under the Pivot tab are also shown in the Sort tab. You can sort the data by rows and by columns using the By rows and By columns sections.
You can sort data in ascending or descending order, or perform a custom sort by selecting the options in the By columns and By rows sections.
You can view a preview of the table by clicking the Preview button. Click Reset to set back to the default selection (Ascending).
For example, you can select the Ascending and Descending options in the Product category and Region fields in the By columns sections, and select the Ascending option in the Customer Name filter in the By rows section to sort the Sales Data. The data sorted will look like as shown in the preview below.
You can also customize the arrangement by selecting the Custom option. You can drag and drop or use the up and down arrows to arrange the rows and columns.
Note: The filter and sort functionalities are optional.
5. You can add fields to the Data section, and choose one of the aggregate functions. These functions can get you the sum, count, average, etc. depending upon the data type of the fields added.
The list of aggregate functions available for each data type is listed in the table below. Click here to know more about aggregate functions.
Data type | Functions | Advanced functions |
Number | Normal | Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value |
Running total | ||
Minimum | ||
Average | ||
Standard deviation | ||
Median | ||
Mode | ||
Percentile | ||
Variance | ||
Count | ||
Distinct count | ||
Text | Count | Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value
|
Distinct count | ||
Collect as list | - | |
Collect as set | ||
List | Count | Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value |
Distinct count | ||
Map | Count | Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value |
Distinct count | ||
Boolean | Count | Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value |
Distinct count | ||
Date | Count | Normal Running total % of row % of row in group % of column % of column in group % of total Difference from % of difference from % of previous value Lookup value |
Maximum date | ||
Minimum date | ||
Distinct count | Year Quarter and year Month and year Week and year Full date Date & time Quarter Month Week Day of the week Day of the month Hour of day |
Note: The functions for the list data type counts the values and the functions for the map data type counts the keys.
6. Click the Preview button and DataPrep will show a live preview of the pivot transformation.
7. When two or more fields are selected in the Columns box, the column names are joined using a dash or hyphen (-).
8. In the new dataset, you can open the data source settings from the Ruleset pane and edit the pivot configuration.
9. Click Pivot to apply the pivot configuration to your dataset.
Aggregate functions
You can add fields to the Data section and choose one of the aggregate functions. The aggregate functions can get you the sum, count, average, etc., depending upon the data type of the fields added in the Data section. The list of functions available for each data type is explained in the table below:
| Function name | Description |
| Sum | Returns the sum of values from a numeric column. |
| Maximum | Return the maximum value from a numeric column. |
| Minimum | Returns the minimum value from a numeric column. |
| Average | Returns the average of all the values from a numeric column. |
| Standard deviation | Returns the standard deviation based on all values from a numeric column. |
| Median | Returns the middle value of the numeric column. |
| Mode | Returns the value that occurs the highest number of times in a numeric column. |
| Percentile | Returns the percentile of the column. The 50th percentile is configured by default. However, you can modify this value as you need. |
| Variance | Returns the variance of the numeric column. |
| Count | Returns the total number of values from a column. |
| Distinct count | Returns the total number of distinct values from a column. |
Maximum date | Returns the maximum date from a date and date-time column. |
Minimum date | Returns the minimum date value from a date and date-time column. |
| Collect as list | Returns all values from the selected column as a list, preserving duplicates and order. |
Collect as set | Returns a unique set of values from the selected column, removing duplicates. |
Advanced functions
For each basic function such as sum, maximum, minimum, etc., you can further choose advanced summary functions like, running total, % of row, % of previous value, lookup value, etc. Using these functions you can calculate the running total of sales over years or the difference in sales from the previous year etc.,
The advanced functions are explained in the table below:
| Advanced function name | Description |
| Normal | Returns the value of the function applied over a numeric column. This option is selected by default. |
Running total | Returns the sum of sequence of numbers as a running total based on the selected Base field. |
% of row | Returns the percentage of the total for each row. |
% of row in group | Returns the total value of each row within a group as a percentage. |
% of column | Returns the percentage of the total for each column. |
% of column in a group | Returns the total value of each row within a group as a percentage. |
| % of total | Returns the percentage of the total of all the data. |
| Difference from | Returns the difference from the previous value based on the selected Base field. |
% of difference from | Returns the differences from the previous values as a percentage based on the Base field. |
| % of previous value | Returns the percentage of the previous value based on the selected Base field. |
Lookup | Returns the value from any of the previous, next, first or last cell based on the Base field selected. |
Examples
Let's look into an example to understand the above functions better.
Note: The below examples are for the aggregate function, sum. However, similar logic will be applied for all other aggregate functions.
Normal
Returns the value of the function applied over a numeric column. This option is selected by default.
Here's a Sales dataset,
| Product | Month | Sales amount |
| Groceries | January | 500 |
| Groceries | January | 800 |
| Groceries | March | 700 |
| Stationery | January | 1200 |
| Stationery | January | 1500 |
| Stationery | March | 1400 |
And here's the output for sum of sales values
Calculation logic
Total sales value for Groceries in January: 500+800=1300
Total sales value for Stationery in January: 1200+1500=2700
Similarly all other values will be calculated.
Running total
Returns the sum of sequence of numbers as a running total based on the selected Base field.
Here's a sample dataset,
Name | Category | Sales units |
Alice | Fruits | 10 |
Bob | Vegetables | 15 |
Charlie | Dairy | 20 |
Emma | Snacks | 25 |
David | Beverages | 30 |
And here's the output,
| Name | Category | Sales units | Running total |
| Alice | Fruits | 10 | 10 |
| Bob | Vegetables | 15 | 10+15=25 |
| Charlie | Dairy | 20 | 25+20=45 |
| Emma | Snacks | 25 | 45+25=80 |
| David | Beverages | 30 | 80+30=110 |
% of row
Returns the percentage of the total for each row.
Here's a Sales dataset,
| Product | Month | Sales amount |
| Groceries | January | 500 |
| Groceries | February | 800 |
| Groceries | March | 700 |
| Stationery | January | 1200 |
| Stationery | February | 1500 |
| Stationery | March | 1400 |
Pivot input
Columns - Month
Rows - Product
Data - Sales amount
Aggregate function - Sum
Advanced function - % of row
Output
Formula
% of row = Sales amount for a month/Row total*100
Calculation logic
Groceries in February=800/2000*100=40.00%
Stationery in January=1200/4100*100=29.27%
Similarly all other values will be calculated.
% of row in group
Returns the total value of each row within a group as a percentage
Here's a Sales dataset,
Pivot input
Columns - Sub-category
Rows - Category
Data - Sales amount
Aggregate function - Sum
Advanced function - % of row in a group
Note: Group is automatically identified depending on the field added to the Rows shelf. The values from this field that are similar form a group.
Output
Formula
% of row in group=Value/Group Total*100
Calculation logic
To find the group total,
Electronics total: 500+700+300=1500
Furniture total: 400+600+500=1500
% of row in Accessories
300/1500*100=20.00%
% of row in Chair
400/1500*100=26.67%
Similarly all other values will be calculated.
% of column
Returns the percentage of the total for each column
Here's a Sales dataset,
| Product | Month | Sales amount |
| Groceries | January | 500 |
| Groceries | February | 800 |
| Groceries | March | 700 |
| Stationery | January | 1200 |
| Stationery | February | 1500 |
| Stationery | March | 1400 |
Pivot input
Columns - Month
Rows - Product
Data - Sales amount
Aggregate function - Sum
Advanced function - % of column
Output
Formula
% of column=Value/Column total*100
Calculation logic
To calculate the total for each column
February: 500+1200=1700
January: 800+1500=2300
March: 700+1400=2100
% of Groceries in February: 800/2300*100=34.78%
% of Stationery in February: 1500/2300*100=65.22%
Similarly all other values will be calculated.
% of column in a group
Returns the total value of each row within a group as a percentage
Here's a Sales dataset,
Pivot input
Columns - Store
Rows - Product
Data - Sales(in $)
Aggregate function - Sum
Advanced function - % of column in a group
Note: Group is automatically identified depending on the field added to the Columns shelf. The values from this field that are similar form a group.
Output
Formula
% of column in a group=Value/Group column total*100
Calculation logic
To calculate the total of each column in a group
Total Sales for Store A: 500+300+200=1000
Total Sales for Store B: 600+400+300=1300
Total Sales for Store C: 700+500+400=1600
% of Electronics in Store A: 500/1000*100=50%
% of Electronics in Store B: 600/1300*100=46.15%
Similarly all other values will be calculated.
% of total
Returns the percentage of the total of all the data
Here's a Sales dataset,
| Product | Month | Sales amount |
| Groceries | January | 500 |
| Groceries | February | 800 |
| Groceries | March | 700 |
| Stationery | January | 1200 |
| Stationery | February | 1500 |
| Stationery | March | 1400 |
Pivot input
Columns - Month
Rows - Product
Data - Sales amount
Aggregate function - Sum
Advanced function - % of column in a group
Output
Formula
% of Total= Value/Grand total*100
Calculation logic
To find the grand total: 500+800+700+1200+1500+1400=6100
Total % of Groceries in February: 800/6100*100=13.11%
Total % of Stationery in February: 1500/6100*100=24.59
Similarly all other values will be calculated.
Lookup
Returns the value from any of the previous, next, first or last cell based on the Base field selected
Here's a Sales dataset,
| Product | Month | Sales amount |
| Groceries | January | 500 |
| Groceries | February | 800 |
| Groceries | March | 700 |
| Stationery | January | 1200 |
| Stationery | February | 1500 |
| Stationery | March | 1400 |
Pivot input
Columns - Month
Rows - Product
Data - Sales amount
Aggregate function - Sum
Advanced function - Lookup value
Compare with - Previous value
Select base field to apply - Rows>Product
Output
Logic for Groceries in February
For Groceries, where Month=February, no previous sales amount value is available before that row, so no value is returned.
Logic for Stationery in February
For Stationery, where Month=February, 800 is the previous sales amount value available in one of the previous rows, so 800 is returned.
Similarly all other values will be calculated.
Note: There is no group concept with Lookup function. It looks for the value you select in the Compare with (Example: previous value) drop down.
SEE ALSO