Window Function

The Window Function column property lets you perform calculations across a set of rows related to the selected row. Unlike aggregate functions, window functions preserve the identity of each row — results are calculated for each row without grouping them into a single output row. If the Partition By value is not on the same level as the view, SEI may need to force-load all view data (including on-demand data) before applying the window function.

Window functions are only supported with Microsoft SQL Server and Oracle data sources.

Perform a window function calculation

important

Ensure the Subtotal property in General is set to any option other than None. Otherwise, the window function will not be applied.

1. In the Fields Table, select a column.
2. Expand the General tab.
3. Next to Window Function, click the button to open the dialog.
4. In Over By, select a function from the dropdown list.
5. Complete the other fields as required: Partition By, Order By and Sort By.
6. Click Ok.
7. On the File tab, click Save Data Model.

Window function options

Option	Description
Over By	Defines the window function calculation type. Use aggregate, ranking, analytic, or statistical functions to calculate values across rows.
Partition By	(Optional) Divides the query result set into partitions based on selected dimensions. Selecting one or more dimensions from the dropdown partitions the result set by those dimensions. Select #Dynamic to partition automatically by the view level.
Order By	Defines the order in which the query result set is processed. #Current Measure Value will use the measure in the grid for ordering. You can also choose one or more dimensions to order the results.
Sort By	(Optional) Specifies the sort direction: ASC, DESC, or NONE.

Over By functions

Aggregate functions

Function	Desription
SUM	Calculates the sum of all numeric values, ignoring NULL.
AVG	Calculates the average of all numeric values, ignoring NULL.
COUNT	Counts rows. COUNT(*) includes NULL values, while COUNT(expression) counts only non-NULL values in that expression.
MIN	Returns the smallest numeric value, ignoring NULL.
MAX	Returns the largest numeric value, ignoring NULL.

Example – Aggregate

To reproduce this example, open the field’s Window Function properties and set:

• Over By to SUM
• Order By to the chronological field (e.g., Month, Date)
• Partition By to reset totals (e.g., Fiscal Year)

A running total is the cumulative sum of a sequence of numbers, updated as new numbers are added. The SUM window function continuously adds values based on the Order By setting. You can restart the summation using Partition By (for example, resetting totals per fiscal year).

Fiscal year running totals:
The SUM function calculates a continuous total until the Partition By (fiscal year) changes, then restarts.

Inventory quantity over time:
The SUM function tracks cumulative inventory levels over dates, showing how stock builds over time.

Ranking functions

Function	Desription
CUME_DIST	Calculates the relative rank of the current row within a partition: (number of rows preceding or equal to the current) / (total rows in partition).
DENSE_RANK	Ranks values within a group without gaps in rank numbers for ties.
NTILE_4 / NTILE_100	Divides rows in each partition into 4 or 100 ranked groups, as evenly as possible.
PERCENT_RANK	Calculates the percent rank using (rank - 1) / (rows in window - 1).
RANK	Ranks values within a group; ties cause gaps in numbering.
ROW_NUMBER	Sequentially numbers rows within a partition; ties receive different numbers nondeterministically.

Example – Ranking

To reproduce these examples, open the field’s Window Function properties and set:

• Over By to ROW_NUMBER, RANK, or DENSE_RANK for ordering
• Over By to PERCENT_RANK or CUME_DIST for distribution analysis
• Choose the dimension or measure for Order By that matches the scenario

Ranking functions let you order, compare, and group rows based on a measure or dimension. They are useful for identifying extremes, spotting trends, and calculating distribution metrics without aggregating the rows.

Identifying highest and lowest sales:
Highest and lowest sales amounts identified using ranking functions. This makes it easy to highlight top performers and lowest values in the dataset. ROW_NUMBER assigns a sequential number to each row after sorting by sales amount. RANK assigns ranks to ties, which may produce gaps in numbering. DENSE_RANK assigns ranks without gaps for ties.

Understanding distribution of values:
Value distribution expressed as percentage rank and cumulative distribution. A row with Percent Rank 0.94 indicates that 94% of values are less than this row’s amount. A row with CUME_DIST 0.47 means 47% of values are less than or equal to this amount.

Value functions

Function	Desription
LAG	Returns the value from the row before the current one; returns NULL if none exists.
LEAD	Returns the value from the row after the current one; returns NULL if none exists.
FIRST_VALUE	Returns the value from the first row in the window frame.
LAST_VALUE	Returns the value from the last row in the window frame.

Example – Value

To reproduce this example, open the field’s Window Function properties and set:

• Over By to LAG or LEAD depending on direction
• Order By to the chronological field (e.g., Month, Date)
• Apply calculations on the returned lag/lead values in your view

The LAG function fetches data from the previous row, while LEAD fetches from the next row. These functions can be used for time-based calculations or comparisons between sequential records—such as growth percentages over time, variance analysis, or tracking delays between tasks.

Comparing monthly sales:
*We calculate the Growth % in sales between months by using LAG and LEAD in the same row. Lead Amount shows the next month’s sales; Lag Amount shows the previous month’s sales. Growth % = (Lead Amount – Lag Amount) / Lag Amount *

Tracking gaps between project tasks:
By measuring days between task start and end dates, we can identify lag time between activities or early starts (leads) that overlap. Lag of 46 days between tasks T-001/02 and T-002 identified.

Statistical functions

Function	Desription
STDEV	Calculates the sample standard deviation of numeric values.
STDEVP	Calculates the population standard deviation of numeric values.
VAR	Calculates the sample variance of numeric values.
VARP	Calculates the population variance of numeric values.

note

The statistical function names are for Microsoft SQL Server. For Oracle data sources, they map to STDDEV, STDDEV_POP, VARIANCE, and VAR_POP.