Using table calculation functions and operators to create window functions

[chris_seymour1]

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:

https://community.looker.com/explores-36/creating-a-window-function-inside-a-table-calculation-custo...

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.

[Vojtech1]

 Mind that this approach fails when there’s more than 1 dimension and the  end user resorts the table. This is a big no no that can lead to false insights !

[etienne_goldste]

aka WINDOW_SUM.