IMPORTANT: This document is specific to Sigma worksheets. To learn about this topic for Sigma’s new Workbooks feature, please visit Working with Pivot Tables.

 

Summary of Content

Create a Pivot Table
Plot a Pivot Table
      Aggregatie Columns
      Add Subtotals
Format a Pivot Table
Sort Columns and Rows
      Define Sort Order and Sort By
      Sort Pivot Tables with Groups
Export a Pivot Table
    Export Results & Limitations
Related Resources

Create a Pivot Table

  1. Open the Visualization tab in the right hand panel of your worksheet.
    Screen_Shot_2020-05-12_at_5.43.57_PM.png
  2. Click + button to add a new visualization. This will add a new visualization and open the visualization editor.
  3. Under VISUALIZATION TYPE, select pivot table by clicking on the Screen_Shot_2020-05-12_at_5.51.22_PM.png icon.
    Screen_Shot_2020-05-12_at_5.51.10_PM.png
  4. Under the Plot tab, select worksheet columns to include as fields in your pivot table.
    Your table must include at least one Row or Column and one Value.
    Columns added to the Value field will automatically be aggregated
    Screen_Shot_2020-05-12_at_6.08.54_PM.png        Screen_Shot_2020-05-12_at_6.02.02_PM.png        Screen_Shot_2020-05-13_at_9.35.43_AM.png
  5. After populating the above fields, you will have a pivot table! The Plot and Format tabs in the visualization editor will allow you to further customize it.
    Screen_Shot_2020-05-13_at_9.34.10_AM.png

 

Plot a Pivot Table

To begin plotting your pivot table, follow the instructions for creating a pivot table.

Pivot tables must include at least one Row or Column and one Value. Multiple columns can be added to any of these fields.

To customize your plotted fields further, open the caret menu on the field. From here, you can take actions such as column formatting, sorting, adding subtotals, and aggregating.

The actions you can take on a column depend on which type of field it belongs to. For example, you can add subtotals to columns under the ‘Row’ and ‘Column’ axis fields but not under ‘Values’.

Screen_Shot_2020-05-13_at_10.12.48_AM.png

Aggregate Columns in a Pivot Table

Columns added to a pivot table’s ‘Value’ field will automatically be aggregated. Numeric columns receive a Sum aggregation, while text and date columns are aggregated by Count.

To change an aggregation:

  1. Open the caret menu on the field.
  2. Hover your cursor over Aggregation to open the aggregation sub-menu.
  3. Select a new aggregation type.

    Screen_Shot_2020-05-13_at_9.45.07_AM.png

 

Add Subtotals to a Pivot Table

Add an extra level of analysis to your pivot tables by creating row and column totals. These subtotals will inherit the aggregation used on the Value field in your pivot table.

  1. Open an existing pivot table or create a new one.
  2. Open the Plot tab in the visualization editor.
  3. Click to open the caret menu on the plotted column you would like to see totals for. This column must belong to the table’s Row or Column field.
  4. In the dropdown menu, select Show Totals.

    Screen_Shot_2020-05-13_at_11.24.35_AM.png
  5. Your new subtotals should now display on your pivot table.
    Repeat steps 3 and 4 to generate any additional Row or Column subtotals.

    Screen_Shot_2020-05-13_at_11.25.56_AM.png

Format a Pivot Table

To begin plotting your pivot table, open the Format tab in your visualization editor.

This tab will allow you to customize the visual aspects of your table. Examples of customizations you can make include renaming the table, setting color schemes, and adding conditional formatting.
Screen_Shot_2020-05-13_at_10.27.22_AM.png

Sort Columns and Rows

Define Sort Order and Sort By

By default, pivot table columns and rows are sorted Ascending by data source order.

  1. Open the dropdown menu on the column or row field you wish to sort. 
    This can be done from either the Visualization panel Plot tab or the pivot itself. 
    Screen_Shot_2020-09-24_at_4.00.18_PM.png
  2. In the dropdown menu, you can select a Sort Order and Sort By criteria. Suggested "sort by" options include the original data source order, row count, and the table's Value field (see "Store Total Sales" in the screenshot above).
    To instead create a custom sort, click Custom Sort.... This will open the pivot table sort modal.
  3. Select a Sort Order.
  4. Under Sort by select 'field'.
    Note:  The Custom Sort Modal also provides the same Sort by parameters as are available in the field's dropdown menu.
  5. Select a worksheet column or visualization field from the provided list.
  6. If you selected a worksheet column, you will be prompted to select an aggregation.Screen_Shot_2020-09-28_at_1.37.01_PM.png
  7. Click Apply to sort your pivot table.

Sort Pivot Tables with Groups

Some pivot tables contain multiple grouped worksheet columns plotted on the Column and/or Row field(s). The screenshot below shows an example of this scenario.

In this example, the fields “Company” and “Customer Name” are both plotted as Rows with “Company” positioned higher than “Customer Name”. In the case of companies “Proin LLC” and “Ac Consulting”, there are multiple customers per company. The Value cells indicate each customer’s total invoices (“sum(Invoice Id)”) per year (“extractYear(Invoice Date)”).

Screen_Shot_2020-10-02_at_2.43.39_PM.png

When sorting your pivot table, it is important to consider how data groupings impact sort results. If we return to the example above, sorting “Company” by the Value field “sum(invoice Id)”) will sort the list of companies by the total customer invoices for each company.

Please note: Default sorting is based on the rows’ max aggregations. Other aggregations can be applied on worksheet pivot tables from the Customer Sort modal. Dashboards DO NOT support custom sort.

The screenshot below demonstrates this sort order. A row Total column has been added to display the aggregated max values used in determining sort order.

Screen_Shot_2020-10-02_at_3.23.58_PM.png

Rows within groupings can also be sorted in relation to other rows within the group. In our example, we will now sort "Customer Name" by “sum(invoice Id)”. The sort order on “Company” is retained, while each group's rows respond to their new sort order.

Screen_Shot_2020-10-02_at_4.02.27_PM.png

Export a Pivot Table

You can download pivot tables and other visualizations from Worksheets and Dashboards.

Scheduled exports of your visualizations can also be created from the schedule modal in your Worksheet.

Learn how to download visualizations from a worksheet.
Learn how to download visualizations from a dashboard.
Learn how to schedule exports from worksheets.

in_sigma.png
Compare this table in Sigma to the export results below.

Export Results & Limitations

Pivot tables exported for Excel (.xlsx) will reflect most structural and formatting options applied to your pivot table in Sigma. The only limitations are:

excel_export.png

Pivot tables exported as CSVs will reflect the applied structural formatting. The limitations are:

csv_export.png

Related Resources

Adding Visualizations to a Dashboard

 

IMPORTANT: This document is specific to Sigma worksheets. To learn about this topic for Sigma’s new Workbooks feature, please visit Working with Pivot Tables.