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:

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

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

  3. 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)

ORDERDATE
SALES
MARKET

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)

ORDERDATE
ASIA PACIFIC
AFRICA
LATAM
EUROPE
USCA

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 Market column 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:

  1. Click Apply to refresh the dataset.

  2. The system will regenerate the data structure and update the visualization.

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