Window Function

Overview

The Window Function column property allows the user to perform calculations across a set of table rows that are related in some way to the selected row. Window Function calculations are similar to Aggregate Function calculations but with the unique distinction that they do not cause the calculated rows to be grouped into a single output row. All rows involved in a Window Function calculation retain their separate identity.

Note  

The Window Function feature is only compatible with MS SQL and Oracle-type data sources.

 

Setup

To perform a Window Function calculation, the user first needs to navigate to the Data Model Designer by right-clicking on a Data Model entry and selecting Design Data Model (as shown below).

 

You can find the Window Function row in the General tab of the Data Model Designer. As with other column properties, if you click on the checkbox that appears after clicking on a cell in the Window Function row, a pop-up window will appear allowing you to set up your calculation (shown below).

The Window Function pop-up window contains the 4 following drop-down fields:

  • Over By - Defines the window function calculation type (Mandatory Parameter)
  • Partition By - Defines the dimensions by which the query result set is divided into partitions (Optional Parameter)
  • Order By - Defines the order in which the query result set will be organized (Mandatory Parameter)
  • Sort By - Defines the sorting order of the calculation, i.e., ASC, DESC or NONE (Optional Parameter)
Important  

The column's Subtotal must have a value other than None (e.g., Sum, Min, Max, Count, etc.). If the column's Subtotal value is None, the Window Function cannot be applied.

Note  

Occasionally, SEI requires a force load of all View data (including on-demand data) when applying a Window Function. This is caused by the selected Partition By value not being on the same level as the View itself.

 

Over By Clause Values

Aggregate Window Functions

  • SUM - The SUM() window function calculates the sum of all input values in the expression. This function can only be applied to numeric values, and it ignores NULL values.
  • AVG - The AVG window function calculates the average of all input values in the expression. This function can only be applied to numeric values, and it ignores NULL values.
  • COUNT - The COUNT() window function calculates the total number of input rows.
    • COUNT(*) calculates the total number of rows in the target table, regardless of whether or not they include NULL values.
    • COUNT(expression) calculates the number of rows that have values other than NULL in a specific column or expression.
  • MIN - The MIN() window function calculates the minimum value of all input values in the expression. This function can only be applied to numeric values, and it ignores NULL values.
  • MAX - The MAX() window function calculates the maximum value of all input values in the expression. This function can only be applied to numeric values, and it ignores NULL values.

Ranking Window Functions

  • CUME_DIST - The CUME_DIST() window function calculates the relative rank of the current row within a window partition (i.e., the number of rows preceding or on the same level as the current row / the total number of rows in the window partition).

  • DENSE_RANK - The DENSE_RANK() window function calculates the rank of a value within a group of values based on the Order By expression and the OVER clause. Please note that values are ranked only within their partition, rows with equal values are assigned the same rank, and there are no gaps in the ranked value sequence if two or more rows have the same rank.

  • NTILE_4 / NTILE_100 - The NTILE window function divides the rows in each window partition, as evenly as possible, into a specified number of ranked groups (i.e., 4 or 100). This function requires an Order By clause in the OVER clause.

  • PERCENT_RANK - The PERCENT_RANK() window function calculates the percent rank of the current row using the following formula (x - 1) / (the number of rows in the window)

  • RANK - The RANK window function calculates the rank of a value within a group of values. The Order By expression in the OVER clause determines the ranked value, and each value is ranked within its partition. Any rows of equal value based on the ranking criteria are assigned the same rank. Drill adds the number of tied rows to the tied rank in order to calculate the following rank. For that reason, ranks may not be consecutive numbers.

  • DENSE_RANK - The DENSE_RANK window function works exactly like the RANK window function but differs in that there will not be any gaps if two or more rows are tied.

  • ROW_NUMBER - The ROW_NUMBER window function calculates the ordinal number of current rows within a partition. The number is determined by the Order By expression in the OVER clause. Each value is ordered within its partition, and rows of equal value as per the Order By expression are assigned different row numbers nondeterministically.

Value Window Functions

  • LAG - The LAG() window function returns the value of the row before the current row in the partition. If no row before the current row exists in the partition, a NULL value is returned.

  • LEAD - The LEAD() window function returns the value of the row after the current row in the partition. If no row after the current row exists in the partition, a NULL value is returned.

  • FIRST_VALUE - The FIRST_VALUE window function returns the value of the specified expression in relation to the first row that appears in the window frame.

  • LAST_VALUE - The LAST_VALUE window function returns the value of the specified expression in relation to the last row that appears in the window frame.

Statistical Window Functions

  • STDEV - The STDEV window function calculates the statistical standard deviation of all values in the specified expression.

  • STDEVP - The STDEVP window function calculates the statistical standard deviation of the population of all values in the specified expression.

  • VAR - The VAR window function calculates the statistical variance of all values in the specified expression.

  • VARP - The VARP window function calculates the statistical variance of the population of all values in the specified expression.

Note  

The above statistical window functions are listed by their MS SQL names. When using an Oracle-based data source, these functions will be replaced by their Oracle equivalents (i.e., STDDEV, STDDEV_POP, VARIANCE and VAR_POP).

Partition By Values

Partition by Dynamic

If the Dynamic value is selected from the Partition By drop-down list, the result set will be automatically partitioned by the View level. For a View with no pivots, that would generally be the parent level.

Partition by Dimension

If one or more dimensions are selected from the Partition By drop-down list, the result set will be partitioned by those selected dimensions.

Order By Values

Order by Current Measure Value

If you select Current Measure Value from the Order By drop-down list, the query result set will be ordered by the current measurement field to calculate the window function.

Example  

You have 12 rows and your data is grouped by month, therefore, you have one row per month. If you apply a SUM() window function where the Order By field is set to Current Measure Value, the 12 monthly values will be added together in order of smallest-to-largest value rather than in sequential order.

Order by Dimension

If one or more dimensions are selected from the Order By drop-down list, the result set will be ordered by those selected dimensions.