Pivot table

Pivot tables provide the flexibility to filter and group results without making any updates to the underlying SQL. This pivot table also includes some nifty formatting options like a heat map and table bar chart.

Across the top of the display you'll find a row of variables. Drag these to the left sidebar to set them as rows in your results. Drag them to the second horizontal bar to set them as columns. Click the down arrow next to a variable to create a filter based on it. If there are too many options for a specific variable to list, you can instead create a filter for it using parameters.

You'll find three drop-down menus in the top-left corner. The first allows you to set the formatting for your display table.

The next drop-down menu allows you to set the aggregation for your results. Choose from a variety of options including count, sum, and average. The final drop-down menu allows you to indicate which values to aggregate and display.

The example below features a heat map-formatted pivot table with information on NFL touchdowns. In this case, the rows are set as the teams, and columns are set as quarters and game locations. The results reference the average age of players scoring a touchdown.

View Mode Analysis

Click Powered by Mode to duplicate the report and create your own pivot table. Learn more about using HTML to customize your reports.

Create a pivot table

Add the style tag at the top of the HTML:

<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.0.2/pivot.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.0.2/pivot.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.0.2/tips_data.min.js"></script>
<link rel="stylesheet" href="https://mode.github.io/alamode/alamode.min.css">
<script src="https://mode.github.io/alamode/alamode.min.js"></script>

This tag calls back to an open-source library called alamode.js, which provides a way for users to manipulate reports with fewer lines of code.

Add the customizable snippet at the bottom of the HTML:

<script>
  alamode.pivotTable(
	{
  	  query_name: "Query 1",
  	  default_columns: ["quarter","location"],
  	  default_rows: ["team"],
  	  default_values: "age",
  	  title: "NFL Pivot",
  	  aggregate_function: "Average",
  	  pivot_table_type: "Heatmap",
  	  editable: true

  	  // Options for aggregate_function:
  	  //    "Count"
  	  //    "Count Unique Values"
  	  //    "List Unique Values"
  	  //    "Sum"
  	  //    "Integer Sum"
  	  //    "Average"
  	  //    "Minimum"
  	  //    "Maximum"
  	  //    "Sum over Sum"
  	  //    "80% Upper Bound"
  	  //    "80% Lower Bound"
  	  //    "Sum as Fraction of Tota
  	  //    "Sum as Fraction of Rows"
  	  //    "Sum as Fraction of Columns"
  	  //    "Count as Fraction of Total"
  	  //    "Count as Fraction of Rows"
  	  //    "Count as Fraction of Columns"

  	  // Options for pivot_table_type:
  	  //    "Table"
  	  //    "Table Barchart"
  	  //    "Heatmap"
  	  //    "Row Heatmap"
  	  //    "Col Heatmap"
	}
  )
</script>

Here are some notes for updating the customizable snippet. To see the source code powering this snippet, click here.

• html_element: If not provided, the map will be added as the last element in the report. To place it elsewhere, select an element in your report with this parameter.
• query_name: The name of the query that returns the pivot table's dataset. If you update the name of the query, you’ll need to update it here as well.
• default_columns: A list of the query column names that you want to appear as columns in the pivot table.
• default_rows: A list of the query column names that you want to appear as rows in the pivot table.
• default_values A query column that you want to use as the values in the cells of the pivot table.
• title: The pivot table's title.
• aggregate_function: The default function for aggregating values into cells in the pivot table. The list of possible options is shown in the snippet above.
• pivot_table_type: The default formatting option for the pivot table. The list of possible options is shown in the snippet above.
• editable: If true, people viewing your pivot table will be able to edit the columns, rows, and other options (these options won't overwrite the defaults). If false, people viewing your pivot table will only see the defaults that you set.