Skip to main content

Pivots

Pivots dynamically transform and summarize data within a visualization. Use pivots to rearrange data across rows and columns for deeper analysis and clearer insights, making it easier to identify trends, patterns, and key figures.

Pivots are especially valuable for analyzing large datasets when you need quick, interactive exploration. They provide flexibility in structuring data and allow you to see multiple dimensions side by side.

Pivots are available for worksheets and charts.

important

Only Dimensions can be added to the Pivots section.

When to use Pivots​

  • Summarizing data by multiple dimensions: Gain a comprehensive view by displaying different categories, such as company and year, in separate columns.
  • Comparing values across columns: Visualize how measures change for each dimension, such as comparing sales by company for each year.
  • Organizing nested pivot levels: Structure insights by arranging two or more pivoted fields (for example, company and year), with years sorted in descending order so the most recent data appears first within each company group.
  • Quickly identifying top metrics: Focus your analysis by pivoting the data so key figures, such as totals per year or company, are immediately visible in a left-to-right, column-based layout.

Add dimensions to Pivots​

Using the View Organizer​

  1. In the Toolbar panel, select Dimensions and Measures.
  2. Select the dimensions you want to add. Use Shift to select multiple dimensions.
  3. Drag the selected dimensions into the Pivots section.

Using the Properties menu​

  1. In the Toolbar panel, select Properties.
  2. In the Data tab, expand the Pivots section.
  3. Click the Select Pivots dropdown menu, and select the dimensions to add.
  4. Click Add.

➤ Example – Pivot by Company and Year to highlight recent trends
Pivot by Company and then by Year to explore annual performance within each company. By sorting Year in descending order, you ensure that the most recent year appears first inside each company group, allowing for fast trend analysis.

Configure Pivots settings​

Show Prompts​

Prompts let you filter and select values for a grouped dimension. A prompt is available if it has been configured for that field in the Data Model Designer.

You can access prompts from either the View Organizer or the Properties menu.

Using the View Organizer​

  1. Right-click a dimension in the Pivots section.
  2. Select Show Prompts.
  3. (Optional) Select Apply to filter the data without closing the dialog.
  4. Select the values you want to filter by, then select Confirm.

Using the Properties menu​

  1. In the Toolbar panel, select Properties.
  2. In the Data tab, expand the Pivots section.
  3. Select and expand the dimension.
  4. Click the prompts icon next to Show Prompts.
  5. (Optional) Select Apply to filter the data without closing the dialog.
  6. Select the values you want to filter by, then select Confirm.

Forcing Values​

Use Forcing values to designate specific dimension values that must always appear in the pivot, even if the underlying query returns no data for those values. This ensures consistency in your reports, prevents missing or inconsistent columns when data is sparse, and supports easier comparisons.

When the forcing values are set, columns or rows appear based on the Sort Order configured for the dimension.

When to use Forcing Values​

  • Ensuring consistency: Maintain the same layout in the pivot, regardless of filters or data availability.
  • Preventing missing columns: Show all categories (such as months), even if data is not present (for example, "February" appears with zero values if there are no sales).
  • Supporting analysis and comparison: Compare across all expected categories or periods to identify true gaps, not just missing data.
  • Improving visualizations: Avoid shifting columns or rows, supporting better alignment in dashboards and presentations.

Force values for pivoted dimensions​

  1. In the Toolbar panel, select Properties.
  2. In the Data tab, expand the Pivots section.
  3. Select and expand the dimension you want to force values for:
    • To always display a specific year, such as for a Year pivot: Enter 2017
    • To display month values for a specific year in a double pivot (Year, Month): Enter 1;2;3
    • To display a month for another year in addition to the months above: Enter 1;2;3;1[2018]
    • To show a description for a specific value, such as the second month of 2017: Enter 2|Your description
  4. Select Refresh to apply the forced values to the view.

Sort Order​

The Sort Order setting controls whether group values are arranged in Ascending or Descending order. This helps present your data clearly. By default, values are sorted in Ascending order.

You can set the sort order for a grouped dimension using either the View Organizer or the Properties menu.

Using the View Organizer​

  1. Right-click a dimension in the Pivots section.
  2. Select Sort Ascending or Sort Descending.

Using the Properties menu​

  1. In the Toolbar panel, select Properties.
  2. In the Data tab, expand the Pivots section.
  3. Select and expand the dimension.
  4. Next to Sort Order, click the ascending or descending icon.