LinkedIn
Twitter
As of Looker 3.36, we have introduced a offset_list function. This function allows you to create a list from a group of rows in a column, and then aggregate over that list. Read more about how lists work here.
The offset function in table calculations allows you to reference a single value in a previous or following row. For example, you can calculate percent of previous using offset.
By comparison, offset_list allows you to reference a whole group of preceding or following rows in one step, and then perform functions on that group. This is particularly useful for something like a rolling average.
Let’s say I have this table that shows me the number of orders per day:
I want to calculate a rolling 7 day average of the number of orders per day. I can do this by using offset_list to grab the last 7 days of orders.
offset_list takes three arguments: the column you want to grab the offset values from, how far from the current row you want to start the offset, and how long you want the offset list to be. So in this case, I want to take the offset of ${orders.count}, I want it to start at the current row 0, and I want it to go for 7 rows.
Then I can take the average of that list for my rolling average.
mean(offset_list(${orders.count}, 0, 7))
This will give me a rolling average like so:
