Moving Averages in Table Calculations (Community)

View the original community article here

Last tested: Sep 2020

You can make a moving average of a value in Looker with this simple table calc using offset_list:

mean(offset_list(${value},0,7))

For every row, this will give you the average of the 7 rows below. You can change the number of rows (it's the last parameter) to increase your window. You can also change the 0 to wherever you'd like the window to start. Positive numbers will move down, and negative numbers move up. So to get average of the ten values above you, you can do:

mean(offset_list(${value},-10,10))

This Help Center Article explains in more detail

Moving average with dynamic number of rows

We can get another level of flexibility by passing a parameter value into a table calc using a dimension:

parameter: period {    type: unquoted    suggestions: ["1", "2", "3", "4", "5", "6", "7", "10"]  }    dimension: parameter_taker  {    sql: {% parameter period %} ;;    type: string  }

And adapting our rolling average calculation from this:

mean(offset_list(${inventory_items.count},0,7))

to this:

mean(offset_list(${inventory_items.count}, 0, to_number(${inventory_items.parameter_taker})))

Gives us a flexible moving average based on the # of days specified.

Moving average for next n days that meet a certain condition

Suppose you would like to get the mean of the next 7 rows that meet a certain condition, given by a yesno table calc called check. This is tricky because you will need to include enough rows in your offset_list to get 7 rows that meet the condition, as well as the rows that don't meet the condition in between (you cannot skip over rows using offset_list). You can do so using the following formulas.

The first calculation, count_yeses, will count the number of yeses so far in the column:

sum(offset_list(if(${check}, 1, 0), -1 * row() + 1, row()))

The second calculation will get a list of the next seven values of Count for which check = yes, using the count_1s calculation as a guide for where to start and end the list. Finally, take the mean of that list:

mean(offset_list(if(${check}, ${view_name.measure_name}, null), 0, match(${count_yeses} + 7, ${count_yeses}) - row() + if(${check}, 0, 1)))