Window Function

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.

Window functions are classified as follows:

• Aggregate functions

• Value functions

• Ranking functions

• Statistical functions

To perform a Window Function calculation:

1. Navigate to the Data Model Designer by right-clicking a Data Model entry and selecting Design Data Model.

   

2. Scroll down in the Fields table until you find the Window Function row in the General tab.

3. Click a cell in the Window Function row.

4. Click the checkbox that appears. A pop-up window is displayed so that you can set up your calculation.

   

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

   • Over By (Mandatory) - Defines the window function calculation type.
   • Partition By (Optional) - Divides the query result set into partitions by the chosen dimensions.

     • 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.

     • 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 (Mandatory) - Defines the order in which the query result set will be organized based on the selected dimensions.

     • 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.

     • 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.

   • Sort By (Optional) - Defines the sorting order of the calculation, i.e. ASC, DESC or NONE.

Aggregate Functions

You can view aggregate function examples here.

• 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 Functions

You can view ranking function examples here.

• 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 Functions

You can view value function examples here.

• LAG - The LAG() window function returns the value of the row before the current row in the partition. If there is no row before the current row 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 there is no row after the current row 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 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.