Window functions in DataLens

Window functions are similar to aggregate functions. They allow you to get additional information about the original sample. For example, you can calculate the cumulative total and the moving average or rank values.

The difference is that when calculating window functions, the rows are separate rather than combined into one. The result of the calculation is displayed in each row. The original number of rows does not change. For more information about data aggregation and grouping in DataLens, see Data aggregation in DataLens.

Grouping in window functionsGrouping in window functions

Just like aggregate functions, window functions can be calculated:

• For a single window.
• For multiple windows.

For more information on grouping in window functions, see Grouping.

Grouping for a single windowGrouping for a single window

With this grouping option, the function is calculated for a single window that includes all rows. The grouping type is TOTAL, which enables you to calculate totals, rank rows, and perform other operations that require information about all source data.

Example

You need to calculate the average sales amount (AvgSales) and deviations from it for each category in the city (DeltaFromAvg). The best function for this is AVG:

• AvgSales — AVG(SUM([Sales]) TOTAL)
• DeltaFromAvg — SUM([Sales]) - [AvgSales]

Grouping for multiple windowsGrouping for multiple windows

Sometimes the window function needs to be calculated separately by group rather than across all records. In this case, the WITHIN and AMONG groupings are used.

WITHINWITHIN

WITHIN: Similar to GROUP BY in SQL. It lists all dimensions by which splitting into windows is performed. In WITHIN, you can also use measures. In this case, their values are similarly included in the window grouping.

Example

Calculating the share of each category (% Total) of the total sales amount by city (TotalSales):

• TotalSales: SUM(SUM([Sales]) WITHIN [City])
• % Total: SUM([Sales]) / [TotalSales]

For example, this is the result for the Column chart:

AMONGAMONG

In this case, splitting into windows is performed for all dimensions that are included in the chart grouping but are not listed in AMONG. This grouping type is contrary to WITHIN. When calculating the function, AMONG transforms to WITHIN, which performs grouping by all dimensions that are not listed in AMONG.

For example, for a chart with grouping by the City and Category dimensions, the following measures are the same:

• SUM(SUM([Sales]) AMONG [Category]) and SUM(SUM([Sales]) WITHIN [City])
• SUM(SUM([Sales]) AMONG [City], [Category])and SUM(SUM([Sales]) TOTAL)

This option is provided only for your convenience and is used when you do not know which dimensions the chart will be built across in advance, but you need to exclude certain dimensions from the window grouping.

SortingSorting

Some window functions support sorting, the direction of which affects the calculation value. To specify sorting for the window function:

• Specify dimensions or measures in the ORDER BY section.
• In the chart, move the dimensions or measures to the Sorting section.

Dimensions and measures for sorting are first taken from the ORDER BY section in the formula and then from the Sorting chart section.

Example

You need to calculate the change in the total sales amount (IncTotal) for the entire period, from the earliest to the latest date. To do this, you can use the RSUM function sorted by the Date dimension: RSUM(SUM([Sales]) TOTAL ORDER BY [Date]).

For a Line chart, the result will look as follows:

You will get a similar result if you set the IncTotal measure with the RSUM(SUM([Sales]) TOTAL) formula and add the Date dimension to the Sorting section.

FilteringFiltering

Function values in charts are calculated after applying filters across the dimensions and measures added to the Filters section. For window functions, you can override this order. To do this, specify the dimensions or measures you need in the BEFORE FILTER BY section of the formula. In this case, the function value will be calculated before filtering is applied.

The calculation order is changed when you need to calculate the function value for the original dataset but the chart data is limited by the filter.

Example

You need to calculate the change in the total sales amount (IncTotal) for the period from 17/01/2014 through 11/03/2014. If you add a Date filter and create the RSUM(SUM([Sales]) TOTAL ORDER BY [Date]) measure, the function will be calculated only for the data limited by the filter:

To calculate a function for all data while only displaying the result for a certain period, you need to add the Date dimension to the BEFORE FILTER BY section: RSUM(SUM([Sales]) TOTAL ORDER BY [Date] BEFORE FILTER BY [Date]).

Creating measures for a window functionCreating measures for a window function

You cannot use a dimension directly as the first argument (value in the syntax description) of a window function. You need to first apply an aggregation function to it so that a dimension becomes a measure that can be used in window functions.

For example, let's assume you need to rank sales records by profit over the entire period in a chart with data grouped by the Year and Category dimensions. To do this, you cannot use the RANK([Profit]) formula, where Profit is a dimension. You need to apply an aggregation function first to convert the Profit dimension into a measure. The most suitable aggregate function here is SUM that returns the amount of profit: SUM([Profit]). Next, apply the RANK window function to the resulting measure. The correct resulting formula is RANK(SUM([Profit])).

You can add measures both at the dataset and the chart level. For more information, see Methods to create measures.

To understand what aggregate function to select for converting dimensions into measures, specify what resulting measure you want to get using a window function. For example, in a chart with data grouped by product Category, you need to order records by Sales. To order records by sales amount, choose the SUM aggregate function: SUM([Sales]). To order them by sales count, choose COUNT: COUNT([Sales]).

If you need to get a string measure with a value determined by grouping and sorting data in a window function, use the ANY aggregate function.

FAQFAQ