Domo IDEAs Conference - Beast Modes - Rolling Averages

GrantSmith
GrantSmith Indiana 🔵

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

Comments

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!