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)))
This content is subject to limited support.