w Function - Window Calculations at the Intermediate Aggregation Level

• Dec 11, 2019
• 2 minutes to read

Overview

The w function introduced in 19.2 allows use window calculations for aggregations at the intermediate aggregation level.

The w function is an argument for the aggr function. The w function syntax specifies a window function, partitioning and ordering:

w(WindowExpression, partitionByFunction, orderByFunction)

Examples

The expression below calculates the top N products by category.

aggr(w(RankDense(Sum([ProductSales]), 'desc'), PartitionBy([CategoryName]), OrderBy()), [CategoryName], [ProductName])

Note

The complete sample project How to Use the W Function to Show Top N Products by Category is available in the DevExpress Examples repository.

• The CategoryName and ProductName fields define that the aggregation - aggr() function - is performed for each category and product.
• The first parameter of the aggregation is the w() function expression. This is a window function that calculates the rank of the Sum([ProductSales] in a window.
• The w() function partitions data by CategoryName. The order of records in a window does not matter in this particular case.

The expression partitions data rows into groups by CategoryName, and calculates a rank of row in a group, and sorts ProductSales totals in descending order. Ranks are calculated for each CategoryName and ProductName combination.

Tip

Another complex example is the
Window Calculations module in the in the WinForms Dashboard demo centre. It uses the w function to highlight the maximum difference between product sales for a specific period.