Window calculation

Window calculations

Window calculations allow you to perform operations on measurements within a visual component, and to perform analytical operations such as percentage of total, cumulative sums, comparisons with previous figures.

Function	Description	Example
Function	Description	Example
Index()	Returns the index of the current row in the window.	Index()
Size()	Returns the number of rows in the window.	Size()
Lookup(SummaryExpression, Position)	Returns the value of the expression in a target position specified as a relative offset from the current position.	Lookup(Sum([Sales Amount]), 3)
Last()	Returns the number of rows from the current row to the last row in the window.	Last()
First()	Returns the number of rows from the current row to the first row in the window.	First()

The w function

Thewfunction uses in-window calculations to calculate the values of the data rows in a window defined by specified criteria. It is used in calculated fields.

The wfunction should be used if you want to reuse your calculations in several places, and possibly call them in other calculated fields.

You can use the result of the function as a measure to calculate summary values in a custom way. You can wrap the function in an aggregate expression to use it as a dimension to group, sort or filter data.

The option "Partition by"

We have a grid of sales made by year and month:

We wish to have the cumulated sum of the sales made each year. As an indication here is the grid of the sums of the orders carried out by year:

We will create a calculated field Running sum total following:

w(RunningSum(Sum([Sales Amount])),partitionBy(GetYear([Sales Date])),orderBy(GetMonth([Sales Date])))

We find for the month of December the sum of all the cumulated months, that is 2.33 billion for the year 2019 and 4.04 billion for the year 2020

We can add an additional partition to calculate the cumulative totals first by year, then by quarter:

Here are the results we want to see on our grid:

We will create a calculated field Running sum total by q following:

w(RunningSum(Sum([Sales Amount])),partitionBy(GetYear([Sales Date]),GetQuarter([Sales Date])),orderBy(GetMonth([Sales Date])))

We have our final grid where we find the sum for each end of quater.

Here are some examples of valid expressions:

Expression	Description
paritionBy()	Partition on all data lines
partitionBy([Category], [Product])	A score by product for each category
partitionBy(aggr(Sum(x), y, z))	A complex partition definition that contains a non-aggregate expression.

A parameter cannot be an aggregation.

The option "Order by"

The orderByfunction specifies the order of calculation of the window function. The parameters can be a list or an empty set of expressions that define the order of computation within a partition.

w(RunningSum(Sum([Sales Amount])),partitionBy(GetYear([Sales Date])),orderBy(GetMonth([Sales Date])))

The default order is ascending. If I want to change this direction I have to specify it with the "desc" function in the function as follows:

w(RunningSum(Sum([Sales Amount])),partitionBy(GetYear([Sales Date])),orderBy(desc(GetMonth([Sales Date]))))

Therefore, the cumulative totals are calculated from the last to the first month:

Here are some examples of valid expressions:

Expression	Description
orderBy()	Empty parameters do not specify the order of calculation.
orderBy(Sum([Sales))	The parameter specifies the ascending order and sorts the values by an aggregation expression.
orderBy(asc(Sum([Sales])))	The parameter specifies the ascending order and sorts the values by an aggregation expression.
orderBy(desc(Sum([Sales])))	The parameter specifies the descending order and sorts the values by an aggregation expression.
orderBy(GetYear(Date))	The parameter specifies the descending order and sorts the values by a non-aggregate expression.