Greetings! This is another post highlighting a beast mode from my Domo IDEAs conference session. This one covers how to calculate a rolling average.

Problem:

How do I calculate a rolling average?

Solution:

We can utilize the LAG function to get the prior X values and then divide the sum by the number of values.

Lag:

Returns the prior X row's value.

-- Author:
-- Created:
-- Last Modified:
-- Description:
-- This will get the prior row's value. Special care will need to be taken if you have a missing date
-- and are trying to calculate a rolling average.
-- For example: if you have a week missing and you're looking at the prior 3 weeks then you'e be getting 4,3 and 1 prior weeks instead of 3,2,1 weeks.
LAG(SUM(`random_number`)) OVER (ORDER BY `dt`)

Lead:

Similar to the LAG function but this will look forwards instead of backwards.

-- Author: 
-- Created:
-- Last Modified:
-- Description:
-- NOTE: This will get the next row's value. Special care will need to be taken if you have a missing date
-- and are trying to calculate a rolling average.
-- For example: if you have a week missing and you're looking at the next 3 weeks then you'e be getting 4,3 and 1 next weeks instead of 3,2,1 weeks.
LEAD(SUM(`random_number`)) OVER (ORDER BY `dt`)

Rolling Average:

Take the prior X values using LAG , add them together then divide by X. This example is a 3 day rolling average.

-- Author:
-- Created:
-- Last Modified:
-- Description:
-- NOTE: Calculate a 3 day rolling average by adding the last 3 values, adding them together and then dividing by 3.
-- Special care will need to be taken if you have a missing date and are trying to calculate a rolling average.
-- For example: if you have a week missing and you're looking at the next 3 weeks then you'e be getting 4,3 and 1 next weeks instead of 3,2,1 weeks.
(LAG(SUM(`random_number`), 3) OVER (ORDER BY `dt`) + LAG(SUM(`random_number`), 2) OVER (ORDER BY `dt`) + LAG(SUM(`random_number`), 1) OVER (ORDER BY `dt`)) / 3