Using Pivoting for Multi-Dimensional Analysis
Pivoting allows you to transform raw data into a structured format, making it easier to analyze multiple dimensions in a single chart. By using pivot columns, you can compare values across categories while maintaining a clear and organized data structure.
1. Understanding Pivoting
Pivoting enables you to convert rows into columns, allowing for multi-dimensional analysis. Instead of viewing data in a linear format, you can categorize and compare different segments efficiently.
Without Pivoting: The dataset remains in its original format, displaying values in a linear structure.
With Pivoting: A categorical field is used to split data into multiple columns, making trends and comparisons more visible.
2. Selecting Pivot Columns
To apply pivoting, follow these steps:
Go to the Chart Components Section
This section dynamically loads based on the selected chart type.
For pivot-supported charts, the pivot column option will be visible.
Choose a Column to Pivot On
Click on the Pivot Columns section.
Select a categorical column (e.g.,
Market,Region,Product Category).The selected column’s values will become headers in the data table.
Apply Aggregation to Y-Axis Values
Since pivoting transforms rows into columns, the Y-axis metrics must be aggregated.
Common aggregation methods include:
SUM - Total values across each category.
AVG - Mean value of each category.
COUNT - Number of occurrences.
DISTINCT COUNT - Unique occurrences in each category.
MIN/MAX - Minimum or maximum value for each category.
3. Data Before and After Pivoting
Here’s how the dataset transforms when a pivot is applied:
Before Pivoting (Raw Data Table)
Jan 1, 2012
28,570.44
Asia Pacific
Jan 1, 2012
11,740.89
Africa
Jan 1, 2012
21,833.02
LATAM
Jan 1, 2012
22,477.44
Europe
Jan 1, 2012
14,276.74
USCA
After Pivoting (Transformed Data Table)
Jan 1, 2012
28,570.44
11,740.89
21,833.02
22,477.44
14,276.74
Feb 1, 2012
41,283.40
8,941.92
10,678.33
33,957.75
8,856.61
Mar 1, 2012
36,461.61
3,869.13
17,203.32
24,369.90
53,842.56
As shown above:
The
Marketcolumn is pivoted into individual columns.Each column now represents a category (e.g.,
Asia Pacific,Africa, etc.).The sales values are aggregated for each market by order date.
4. Applying and Updating Pivot Settings
After selecting the pivot column:
Click Apply to refresh the dataset.
The system will regenerate the data structure and update the visualization.
If changes are needed, you can:
Modify the pivot column selection.
Change the aggregation type for Y-axis metrics.
Adjust filters to refine the data further.
5. Best Practices for Pivoting
Choose pivot columns wisely: Use categorical fields that make sense for analysis.
Ensure Y-axis metrics are aggregated appropriately.
Use filters to manage large datasets efficiently.
Always hit Apply after making changes to update the visualization.
By using pivoting effectively, you can analyze data from multiple dimensions in a structured way, making comparisons clearer and insights more actionable.
Last updated
