# Moving Averages in Table Calculations

• 0 replies
• 2555 views

Userlevel 5 • sam • Looker Staff
• 173 replies

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)))``

This content is subject to limited support.