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
