Understand window calculations

Window calculations allow you to perform advanced analysis on measures by calculating values across groups of rows in a report.

This article explains how window calculations work and when to use them.

See Apply window calculations to learn how to apply window calculations in your reports.

In this article:

• What is a window calculation
• When to use a window calculation
• Types of window calculations
• Calculation subtypes
• Window calculation elements
• Configure window calculation elements
• Need more support?

What is a window calculation

A window calculation performs calculations across a group of rows in a report, rather than on each row individually.

A "window" refers to the set of rows included in the calculation, based on how the data is grouped and ordered.

Unlike standard aggregations, which calculate a single value per group, window calculations operate across multiple rows to produce results such as running totals, rankings, or percentages.

When to use a window calculation

Window calculations are useful when you need to analyse how values relate to other rows in a report.

Use a window calculation when you want to:

• calculate a running total
• rank values
• compare a value to a previous or next row
• calculate a percentage of a total
• calculate a moving average
• compare values across time periods

Window calculations build on the results already displayed in the report and only operate on the data included in it. If the required data is not present, the calculation will not return accurate results. For example, a year-over-year comparison requires data from both years to be included in the report.

Types of window calculations

Window calculations are applied to measures and can be configured as either Temporal or Visual calculations.

• Temporal: follows a time-based sequence using a date field
• Visual: follows the current layout of the report, based on how the data is grouped and sorted

Use Temporal calculations for time-based analysis, and Visual calculations when you want results based on how the report is currently organised.

Temporal calculations rely on the available time-based data in the report. Ensure the report includes the required time range for accurate results.

Calculation subtypes

Each window calculation type includes a set of calculation subtypes, allowing you to choose the type of analysis you want to perform.

Percent of total

Computes the percentage of the total for each row.

• Temporal: calculated within a selected time window (day, week, month, quarter, year, or all time)
• Visual: calculated within the current window based on the report’s grouping and layout

Running

Computes a cumulative value across rows.

• Temporal: calculated within a selected time period (WTD, MTD, QTD, YTD, or all time), with options to compare to the previous year
• Visual: calculated across visible rows in the report

Supports Sum, Average, Minimum, Maximum, Count, and Count distinct.

Moving

Computes a value across a sliding range of rows around the current row.

• Temporal: based on a selected time period
• Visual: based on the report’s current order

Supports Sum, Average, Minimum, Maximum, Single, and Count.

Window

Computes a value across all rows within the selected window.

• Temporal: based on a selected time period
• Visual: across all visible rows

Supports Sum, Average, Minimum, Maximum, Single, Count, and Count distinct.

Rank

Assigns a rank to values based on their order.

• Temporal: calculated within a selected time period
• Visual: based on report layout and sorting

Supports:

• Rank: assigns the same rank to equal values, with gaps in rankings
• Dense rank: assigns the same rank to equal values, without gaps
• Unique: assigns a unique rank to each row, even if values are equal
• NTile: groups values into evenly sized buckets (for example, quartiles or percentiles)

Relative value

Displays a value from another position relative to the current row.

• Temporal: compares across time periods (previous, next, first, last, or same period)
• Visual: compares across rows in the report

Difference

Computes the change between the current value and another value.

• Temporal: compares across time periods (difference, growth percentage, or percentage change)
• Visual: compares across rows or groups in the report

Window calculation elements

Window calculations are controlled by three elements that determine how rows are grouped and evaluated.

• Partitioning: groups rows into smaller windows
• Ordering: defines the sequence in which rows are evaluated
• Framing: defines which rows are included in the calculation

Configure window calculation elements

You can control how a window calculation works by configuring its partitioning, ordering, and framing.

Partitioning

Partitioning controls how rows are grouped before the calculation is applied.

Example:

In a report showing Total orders by Month, Product name, and SKU, you apply a Visual Percent of total window calculation to Total orders. By default, no partitioning is applied, so the calculation runs across all rows.

You can adjust the partitioning to control how the calculation is performed. This can be done either by grouping rows based on the order of columns in the report or by selecting specific dimensions.

By columns

When partitioning by columns, a slider appears. You can drag the slider to group rows based on the columns in your report.

• Table reports use a single slider
• Matrix reports use two sliders (one per axis)

By specific dimensions

You can also partition by selecting specific dimensions, allowing you to group rows independently of the column order.

Sorting behaviour

Sorting affects how partitioning is applied:

• Sorting by a dimension changes the report’s hierarchy. The sorted dimension becomes the first field in the slider.
• Sorting by a measure removes the hierarchy. In this case, the slider can’t be used for partitioning, and you’ll need to partition by specific dimensions instead.

Ordering

Ordering defines the sequence in which rows are evaluated within each window.

You can choose:

• Down
• Up
• Custom (evaluates rows based on the selected field or fields)

Example:

In a report showing Total orders by Product name, you apply a Visual Running Sum window calculation to Total orders.

You can choose how the values are accumulated:

• Down: Sums values from the first row to the last row
• Up: Sums values from the last row to the first row
• Custom: Sums values based on the sorting of a selected field

Framing

Framing defines which rows within the partition are included in the calculation.

You can specify:

• how many rows preceding the current row are included
• how many rows following the current row are included

This is commonly used for calculations such as moving averages.

Example:

In a report showing Total orders by Product name, you can apply a Visual Moving Average window calculation to Total orders.

In addition to choosing the order of the calculation (Down, Up, or Custom), you can define how many rows before and after the current row are included in the moving average.