Window Functions

Computes per-row values over a defined subset of rows — enabling rankings, running totals, lead/lag comparisons, and rolling statistics — without collapsing the Dataset.

Window Functions compute a value for each row based on a defined window — a subset of rows determined by partitioning and ordering criteria — without collapsing the Dataset into fewer rows as aggregation functions do. This makes them ideal for computing running totals, rankings, first/last values, lead/lag comparisons, deduplication, and rolling statistics across groups within a Dataset.

Window Functions are accessible via the Actions menu in the Dataset Table view:

1. Open a Dataset in Table view.

2. Click Actions in the top-right toolbar.

   
3. Select Window Functions.

4. Configure the function, partitioning, ordering, and output column settings.

Available Functions

Function	Description
Count	Returns the count of non-null values within the window for each row.
De duplicate	Removes duplicate rows within the defined partition, retaining only one representative row per group based on the specified order.
First value	Returns the first value in the window for each row, based on the defined partition and order.
Last value	Returns the last value in the window for each row, based on the defined partition and order.
Next	Returns the value from the next row within the partition (equivalent to a Lead of 1).
Previous	Returns the value from the preceding row within the partition (equivalent to a Lag of 1).
Rank	Assigns a rank to each row within the partition, with gaps in ranking when there are ties (e.g., 1, 2, 2, 4).
Rolling average	Computes a moving average of a numeric column over the defined window.
Rolling max	Returns the maximum value within the rolling window for each row.
Rolling min	Returns the minimum value within the rolling window for each row.
Rolling sum	Computes a cumulative or rolling sum of a numeric column over the defined window.
Row number	Assigns a unique sequential integer to each row within the partition, starting at 1, regardless of ties.
Standard deviation	Computes the standard deviation of a numeric column over the defined window.

Configuration

Select function

Choose the window function to apply from the dropdown. See the full list above.

Select field

Select the column to apply the window function to. The function is computed over the values in this column within each defined window.

Partitioned by

Define the column(s) used to partition the Dataset into independent groups. The window function is applied separately within each partition — equivalent to PARTITION BY in SQL.

• Click Pick another field for partitioning to add multiple partition columns.

• Click the × icon to remove a partition column.

Example: Partitioning by Billing_State computes the window function independently for each state group.

💡 Note: Partitioning is optional. If no partition column is specified, the entire Dataset is treated as a single window.

Pick order by field

Define the column(s) used to sort rows within each partition before the function is applied. This determines which row is considered "first", "last", or ranked highest within the window.

Click Pick order by field to add one or more ordering columns.

Ignore Null

Controls whether null values in the selected field are excluded when computing the window result.

Option	Behaviour
Yes	Null values are skipped. The function returns the first/last/next/previous non-null value.
No (default)	Null values are included in the window computation.

Create new header

When enabled (default: on), the window function result is written to a new column rather than overwriting the source column.

Column name

The name of the new output column that will contain the computed window function result.

Steps

1. Open a Dataset in Table view.

2. Click Actions and select Window Functions.

3. In Select function, choose the function to apply.

4. In Select field, choose the column to compute the function over.

5. Under Partitioned by, select one or more columns to define the partition groups. Click Pick another field for partitioning to add more.

6. Click Pick order by field to define the sort order within each partition.

7. Set Ignore Null to Yes or No as appropriate.

8. Ensure Create new header is toggled on and enter a Column name for the output.

9. Click Save to apply.

Examples

First value per group: Select First value, partitioned by Billing_State, ordered by Last_Modified_Date ascending — returns the earliest record's field value for each state group in every row of that group.

Deduplication within a group: Select De duplicate, partitioned by company_name, ordered by fiscal_year descending — retains only the most recent record per company, removing earlier duplicates.

Year-over-year comparison: Select Next or Previous, partitioned by company_name, ordered by fiscal_year — Next returns the following year's value alongside the current row; Previous returns the prior year's value, enabling year-over-year comparisons without a self-join.

Rolling statistics for trend analysis: Select Rolling average, Rolling sum, Rolling min, or Rolling max, partitioned by Industry, ordered by Last_Modified_Date — computes moving statistics per industry group across time-ordered rows.

Standard deviation for outlier detection: Select Standard deviation, partitioned by Billing_State, ordered by NumberOfEmployees — computes spread within each state to identify outlier companies by size.