# Using table calculation functions and operators to create window functions

• 0 replies
• 111 views

• Looker Staff
• 39 replies

Not directly - however, you can create window functions by using some complicated table calculation formulas with Looker functions and operators

### Example Code

Here are the formulas. All of the examples use `orders.id `as the grouping column and `products.retail_price` as the column to aggregate. Replace those fields with the fields in your Explore that you would like to group by and aggregate.

group_start_row:

`match(\${orders.id}, \${orders.id})`

next_group_start_row:

`count(\${orders.id}) - match(\${orders.id}, offset(\${orders.id}, count(\${orders.id}) - row()*2 + 1)) + 2`

Once you have starting and ending row, you can make all kinds of functions:

Grouped count:

`\${next_group_start_row} - \${group_start_row}`

Grouped sum:

`sum(offset_list(\${products.retail_price}, -1 * (row() - \${group_start_row}), \${next_group_start_row} - \${group_start_row}))`

Grouped running total:

`sum(offset_list(\${products.retail_price}, -1 * (row() - \${group_start_row}), row() - \${group_start_row} + 1))`

Max date in Group (the date must be sorted desc):

`index(\${products.date}, \${group_start_row})`

### How does it work?

The `match` function gets the row number of the first row that contains the current row's value. So if rows 13-17 contain "foo", the `match` function will return 13 for each of those rows, which gives us the group_start_row calculation. The next_group_start_row calculation reverses the list and applies the `match` function to find the last row for each group, making a few adjustments in the arithmetic to get to the next_group_start_row from there

Once you have start and end rows, it's mostly just using offset_list to get the list of values within each group by using the group_start_row and next_group_start_row as inputs.

See the below topic for some more discussion:

### Interactive example

This Explore on the Looker ​Learn instance is a great way to see the examples in action. If you don’t have access to the Learn instance, registering for this training course will grant you access.