Pivot Tables

What You'll Learn

• How to add and configure pivot tables in your Next-Gen apps
• How to set up row groupings, column groupings, and value calculations
• Best practices for selecting fields and organizing your data analysis
• How to interpret pivot table results and troubleshoot common issues
• Practical examples for sales analysis, performance tracking, and operational metrics

Overview

Pivot tables in Knack provide a dynamic way to analyze your data by creating summaries that can be easily reconfigured without changing your underlying data structure. You can group data by different fields, calculate totals and averages, and present complex datasets in an easily digestible format.

This feature is particularly valuable when you need to analyze trends, compare performance across different categories, or create executive-level summaries from detailed transactional data. Whether you're tracking sales performance across regions, analyzing customer behavior patterns, or summarizing project metrics, pivot tables give you the flexibility to view your data from multiple perspectives.

Important: Classic to Next-Gen Compatibility

❗️

If you have existing pivot tables in Knack Classic, they are not forwards compatible with Next-Gen Apps. Pivot tables created in Classic will need to be rebuilt in Next-Gen. Pivot tables created in Next-Gen Apps will not show in Classic Live Apps. Learn more here.

Quick Start: Your First Pivot Table

Before diving into detailed configuration options, let's walk through creating a basic pivot table to get you started.

Basic Example: Sales by Region

Let's say you have a sales data table with fields for Products, Sales Region, and Transactions. Here's how to create a simple analysis:

1. Add the pivot table element to your page

2. Select your data source (Transactions in this case)

1. In the Data Display tab, configure basic groupings:

Row Group: Sales Category

Column Group: Sales Region

Values: Total Amount (Sum)

Save and view your pivot table

This creates a cross-tabulated view showing total revenue for each product category across different regions, giving you immediate insights into your sales performance.

💡

Tip: Start with this simple configuration, then add complexity like secondary row groupings or additional value calculations once you're comfortable with the basics.

Choosing Your Data Source

Before configuring your pivot table, you'll need to select the table that contains the data you want to analyze. The data source table should contain the fields you plan to use for grouping, categorizing, and calculating values in your pivot table.

Consider the structure of your data when selecting a source table. Pivot tables work best with normalized data where each row represents a single record or transaction. If your data is spread across multiple related tables, you may need to use connection fields to access related information.

Working with Fields and Data Display

The Data Display Panel

The Data Display panel provides access to all available fields from your selected data source table. This panel is organized into sections that help you quickly find and select the appropriate fields for your pivot table configuration.

Row Grouping Fields: This section lists all fields that can be used for organizing your data into rows. You'll see both direct fields from your source table and any connection fields that link to related tables.

Column Grouping Fields: Similar to row grouping, this section shows fields suitable for creating column categories in your pivot table.

Value Fields: This section displays numeric fields and calculated fields that can be used for aggregation and analysis in your pivot table cells.

This structure allows you to analyze relationships between different dimensions of your data. For example, you might group sales data by product category (rows) and region (columns), with total revenue displayed as the values.

Row Configuration

Let's start with setting up how your data will be organized vertically in rows.

Setting Up Row Grouping

Row grouping determines how your data is organized vertically in the pivot table. You can configure multiple levels of row grouping to create hierarchical data organization.

Row Group 2, Customer Type, in this example, provides a secondary level of grouping within each primary group. This creates a hierarchical structure where each primary group can be expanded to show subcategories. For instance, if your primary grouping is by Sales Category, your secondary grouping might be by Customer Type or Sales Channel.

Each row group can be configured with sorting options. You can choose to sort alphabetically (A→Z or Z→A) or numerically, or oldest to newest, depending on the field type. Proper sorting ensures your data is presented in a logical, easy-to-read format.

Although you can create more than 2 row groupings, the total number of groupings cannot exceed 3,000.

Column Configuration

Next, let's set up how your data will be categorized horizontally across columns.

Choosing Your Column Grouping

Column grouping defines how your data is categorized horizontally across the top of your pivot table. Unlike row grouping, column grouping typically uses a single field that creates distinct categories for comparison.

When selecting fields for column grouping, consider the number of unique values in the field. Too many unique values can create a very wide table that's difficult to read, while too few may not provide meaningful analysis.

In this example, we selected Sales Region for the Column Grouping:

Values and Calculations

Finally, let's configure what data will be calculated and displayed in your pivot table cells.

Setting Up Your Value Fields

The Values section determines what data is calculated and displayed in the cells of your pivot table. You can configure multiple value fields to show different metrics simultaneously.

Field Selection: Choose the numeric fields you want to analyze. These might include amounts, quantities, counts, or any other measurable data points in your source table.

Summarization Methods: For each value field, you can select how the data should be summarized:

• Sum: Adds up all values in each group
• Average: Calculates the mean value for each group
• Minimum: Shows the lowest value in each group
• Maximum: Shows the highest value in each group
• Count: Counts the number of records in each group

Display Options: You can configure how each value appears in the pivot table, including custom labels and formatting options. The "Show As" setting allows you to control the display name for each calculated field.

Summary Controls: Enable or disable row summaries grand totals using the "Show summaries" toggle. Summary rows provide grand totals (or average/min/max) for each row and row grouping level.

Column Summaries allows you to show the Sum, Average, Minimum or Maximum for each column. Select one or more.

Available Field Types

Your pivot table can utilize various types of fields from your data source:

Direct Table Fields: These are fields that exist directly in your selected source table, such as dates, categories, amounts, and text fields.

Connection Fields: When your source table has connections to other tables, you can access that direct connection field. This allows you to group or categorize data based on information stored in connected records.

Calculated Fields: Some calculated fields may be available, such as record counts or other system-generated metrics that can provide additional analysis dimensions.

Coming Soon ; System Fields: Knack provides certain system fields like record creation dates or modification timestamps that can be useful for time-based analysis.

Understanding Your Pivot Table Output

Reading the Pivot Table Structure

Once configured, your pivot table presents data in a cross-tabulated format that makes it easy to identify patterns, trends, and relationships in your data.

Row Headers: The leftmost columns show your row grouping hierarchy. Primary row groups appear as main categories, with secondary row groups indented underneath when expanded. This hierarchical structure allows you to drill down from high-level categories to more detailed subcategories.

Column Headers: The top row displays your column grouping categories. Each unique value from your column grouping field becomes a separate column, creating comparison points across your data.

Data Cells: The intersecting cells contain your calculated values based on the summarization methods you've configured. Each cell represents the aggregated data for the specific combination of row and column categories.

Summary Rows: When enabled, summary rows appear at various levels of your row grouping hierarchy, showing totals or other aggregations for each grouping level.

Grand Totals: The rightmost columns and bottom rows may display grand totals that summarize data across all categories, providing overall metrics for your entire dataset.

Interpreting Calculated Values

The values displayed in your pivot table cells depend on the summarization methods you've selected:

Sum Values: Show the total of all records that match the row and column intersection. This is useful for analyzing total sales, quantities, or other cumulative metrics.

Average Values: Display the mean value for records in each intersection. This helps identify typical performance levels or average transaction sizes across different categories.

Count Values: Show how many records exist for each intersection. This is valuable for understanding volume, frequency, or distribution patterns in your data.

Minimum and Maximum Values: Reveal the range of values within each category intersection, helping identify outliers or performance extremes.

Working with Hierarchical Data

When you've configured multiple row grouping levels, your pivot table creates a hierarchical structure that can be expanded or collapsed:

Expandable Groups: Primary row groups can be expanded to show their secondary groupings, allowing you to drill down into more detailed analysis.

Upcoming Features

Knack is continuously enhancing the pivot table functionality in Next-Gen Apps. Several features are currently in development and will be released in future updates:

Source Filters

Source filters will allow you to limit the data included in your pivot table before any grouping or calculation occurs. This feature will enable you to:

• Filter data based on date ranges, categories, or other criteria
• Create focused analyses on specific subsets of your data
• Create multiple pivot tables from the same source with different data scopes

User Filters

User filters will provide interactive filtering capabilities for end users viewing your pivot tables. This functionality will allow users to:

• Dynamically filter pivot table data without rebuilding the configuration
• Apply multiple filter criteria simultaneously
• Create personalized data views based on user roles or preferences

Exporting Capabilities

Export functionality will enable you to share and use your pivot table data outside of Knack. Planned export features include:

• Export to common formats like Excel & CSV
• Maintain formatting and structure in exported files

💡

These features will significantly expand the analytical capabilities of pivot tables and make them even more valuable for business intelligence and reporting workflows. Keep an eye on Knack's release notes for announcements about when these features become available.

Practical Examples and Use Cases

Sales Analysis by Region and Product

One of the most common applications for pivot tables is analyzing sales performance across different dimensions. Consider a scenario where you have transaction data that includes product categories, sales regions, and revenue amounts.

Configuration:

• Row Group 1: Product Category (to see performance by product type)
• Row Group 2: Customer Type (to understand customer segments within each category)
• Column Group: Sales Region (to compare performance across geographic areas)
• Values: Total Amount (Sum), Unit Price (Average)

This configuration creates a comprehensive view showing how different product categories perform across various regions, with the ability to drill down into customer type performance within each category. The summary calculations provide both total revenue and average pricing insights.

Time-Based Performance Tracking

Pivot tables excel at analyzing trends over time periods. For project management or performance tracking, you might analyze completion rates, resource utilization, or quality metrics across different time periods.

Configuration:

• Row Group 1: Project Phase or Department
• Row Group 2: Team Member or Resource Type
• Column Group: Month or Quarter
• Values: Hours Worked (Sum), Tasks Completed (Count), Efficiency Rating (Average)

This setup allows you to track how different teams or departments perform over time, identify seasonal patterns, and compare resource allocation across projects.

Customer Behavior Analysis

Understanding customer patterns and preferences is crucial for business growth. Pivot tables can help analyze customer behavior across different dimensions.

Configuration:

• Row Group 1: Customer Segment or Type
• Row Group 2: Product Category
• Column Group: Purchase Channel or Location
• Values: Purchase Amount (Sum), Transaction Count (Count), Average Order Value (Average)

This analysis reveals which customer segments prefer which products and channels, helping inform marketing strategies and inventory decisions.

Operational Efficiency Metrics

For operational analysis, pivot tables can help identify bottlenecks, efficiency opportunities, and resource allocation needs.

Configuration:

• Row Group 1: Process or Department
• Row Group 2: Status or Priority Level
• Column Group: Time Period or Shift
• Values: Processing Time (Average), Volume Handled (Sum), Error Rate (Average)

This configuration helps identify when and where operational issues occur, enabling data-driven process improvements.

✅

Best Practice: Start Simple - Begin with a basic configuration using one row group, one column group, and one value field. Add complexity gradually once the basic structure is working correctly.

Tips and Best Practices

Planning Your Pivot Table

Define Your Questions First: Before configuring your pivot table, clearly define what questions you're trying to answer. This will guide your field selection and help you create more focused, useful analyses.

Understand Your Data Structure: Familiarize yourself with your source table structure, including field types, data quality, and relationships to other tables. This knowledge will help you make better configuration choices.

Consider Your Audience: Think about who will be viewing and using the pivot table. Different audiences may need different levels of detail or different perspectives on the same data.

Creating Effective Pivot Tables

Choose Meaningful Groupings: Select row and column groupings that create logical, interpretable intersections. The combinations should make business sense and support your analysis goals.

Use Descriptive Labels: When configuring value fields, use clear, descriptive labels that make it obvious what each calculation represents.

Collaboration and Sharing

Create Multiple Views: Consider creating different pivot table configurations for different stakeholder groups or analysis purposes, even when using the same source data.

Provide Context: When sharing pivot tables with others, include explanatory text or documentation that helps viewers understand what they're seeing and how to interpret the results.

Advanced Configuration Tips

Experiment with Summarization Methods: Don't default to sum calculations for all numeric fields. Consider whether average, minimum, maximum, or count might provide more meaningful insights for specific fields.

Use Hierarchical Grouping Strategically: When using multiple row grouping levels, ensure that the hierarchy flows logically from general to specific categories.

Balance Detail and Overview: Configure your pivot tables to provide both high-level insights and the ability to drill down into details when needed.

Next Steps

Once you've mastered the basics of pivot table configuration, consider these advanced applications:

• Create multiple pivot tables from the same data source to provide different analytical perspectives
• Combine pivot tables with other Knack features like forms and workflows to create comprehensive business applications
• Keep an eye out for the upcoming features like source filters, user filters, and exporting capabilities to enhance your analytical workflows

With the foundation provided in this guide, you're ready to start leveraging the full analytical power of pivot tables in your Knack Next-Gen Apps.

Glossary

Aggregation: The process of combining multiple data points using mathematical functions like sum, average, count, etc.

Hierarchical Grouping: Organizing data in multiple levels, where primary groups can be expanded to show secondary sub-groups.

Pivot: To rotate or reorganize data to view it from different perspectives without changing the underlying data structure.

Summarization: The method used to calculate values in pivot table cells (sum, average, count, min, max).

This documentation is based on current Next-Gen Apps functionality. Features and functionality will evolve as Knack continues to enhance Pivot Tables.