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

<table><thead><tr><th width="159.10781517032243">Function</th><th width="150">Description</th><th width="236.2405345211581">Example</th></tr></thead><tbody><tr><td>Function</td><td>Description</td><td>Example</td></tr><tr><td>Index()</td><td>Returns the index of the current row in the window.</td><td><p>Index() </p><p><img src="https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6UZRTfr5YASPGtfa%2FIndex().png?alt=media&#x26;token=a4faeb43-0898-4f49-9d7f-ca87ff3136be" alt="" data-size="original"> </p><p></p></td></tr><tr><td>Size()</td><td>Returns the number of rows in the window.</td><td><p>Size()</p><p><img src="https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6XkhzvWKA0nJUaGO%2FSize().png?alt=media&#x26;token=74e5d8c7-f828-41ed-9678-d24f314e6dcf" alt="" data-size="original"> </p></td></tr><tr><td>Lookup(SummaryExpression, Position)</td><td>Returns the value of the expression in a target position specified as a relative offset from the current position.</td><td><p>Lookup(Sum([Sales Amount]), 3)</p><p><img src="https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6c-bo3gbKwAb0uNv%2FLookup().png?alt=media&#x26;token=073b6630-5aef-4de5-9a94-c0567bf37a19" alt="" data-size="original"> </p></td></tr><tr><td>Last()</td><td>Returns the number of rows from the current row to the last row in the window.</td><td><p>Last()</p><p><img src="https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6eXz8g8m_bt1RbvF%2FLast().png?alt=media&#x26;token=8bb31200-c95f-4bc5-a059-78945edd1097" alt="" data-size="original"> </p></td></tr><tr><td>First()</td><td>Returns the number of rows from the current row to the first row in the window.</td><td><p>First()</p><p><img src="https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6ggQ6urZs-aML85J%2FFirst().png?alt=media&#x26;token=5d103057-4677-463d-b965-e3ebf55ce092" alt="" data-size="original"> </p></td></tr></tbody></table>

## The w function

The`w`function 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**.&#x20;

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

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:

![](https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6j1dP5xEFt8wva1t%2Fgrid1.png?alt=media\&token=38a488f3-682e-40dc-9a33-7551f6c8df92)

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:

![](https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6lH8s1J83EqMozFt%2Fgrid2.png?alt=media\&token=f62c9c6e-f12d-4181-abee-6994c6ec5148)

We will create a calculated field Running sum total following:

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

![](https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6oallqJzFtV3hFly%2FRunningSumTotal.png?alt=media\&token=8863fe2c-f02a-4ea1-8610-84ac62b6c113)

{% hint style="info" %}
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
{% endhint %}

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:

![](https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6uSwIiNcE9T8Lzsw%2Fquarter-year.png?alt=media\&token=aa6111f6-7d3f-426e-a8b4-b37cc65f3807)

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.

![](https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6x9uFBfGfbSsYTD9%2FGridQuarter%2BMonth.png?alt=media\&token=afd0a0ea-cfc3-44a6-9096-aaa9cbf58317)

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

{% hint style="info" %}
A parameter cannot be an aggregation.
{% endhint %}

###

### The option "Order by"

&#x20;The `orderBy`function 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:

![](https://1155922781-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-ML7J3m_7oFtqqYdq0mw%2F-Mje6MbJrZ6NvJQ8HquM%2F-Mje6zRVExU0ux9nADyI%2FRunningSumDesc.png?alt=media\&token=743fb818-329d-4416-b28f-1e1f442bbe71)

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