Domo IDEAs Conference  Beast Modes  Rolling Averages
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
**Did this solve your problem? Accept it as a solution!**
Comments

Here's a link to Grant's entire presentation!
Jae Wilson
Check out my π₯ Domo Training YouTube Channel π¨βπ»
**Say "Thanks" by clicking the β€οΈ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 
@GrantSmith Do yo have a sample beast mode for calculating a 13 week rolling average for on time delivery? Here's what I have currently. Output im trying to achieve is the following columns  Date, Customer, Rolling Avg.
( lag(sum(`OT Shipped`/ `Total Shipped`)) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 2) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 3) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 4) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 5) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 6) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 7) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 8) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 9) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 10) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 11) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 12) over( order by `Customer`,`Week Date Grouping`) + lag(sum(`OT Shipped`/ `Total Shipped`), 13) over( order by `Customer`,`Week Date Grouping`) Β Β )/13
0 
This methods assumes you have no missing weeks. If you miss a week it will cause your calculations to be incorrect. Are you attempting to calculate the rolling average across all your customers or within each customer?
Also with your SUM function the way you're calculating it now is an average of the average which isn't quite correct. You'd want to sum the lag of OT shipped first and then divide all that by the sum of the lag of your total shipped instead of 13
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Hi @GrantSmith
I donβt have any missing weeks (I have a separate date dataset joined). I am trying to calculate for each customer.
Are you suggesting the beast mode below would be correct? Example of a rolling 4 day average
(LAG(SUM(`OT Shipped`)) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`OT Shipped`),2) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2))+LAG(SUM(`OT Shipped`),3) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`OT Shipped`),4) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) / LAG(SUM(`Total Shipped`)) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`Total Shipped`),2) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2))+LAG(SUM(`Total Shipped`),3) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`Total Shipped`),4) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) )/4
0 
You wouldn't need the /4 and your total shipped should be surrounded in a parenthesis.
(LAG(SUM(`OT Shipped`)) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`OT Shipped`),2) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2))+LAG(SUM(`OT Shipped`),3) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`OT Shipped`),4) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) / (LAG(SUM(`Total Shipped`)) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`Total Shipped`),2) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2))+LAG(SUM(`Total Shipped`),3) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)) +LAG(SUM(`Total Shipped`),4) OVER (PARTITION BY `Customer` ORDER BY WEEK(`Date`,2)))
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
 11.4K All Categories
 6 Private Company Board
 2 APAC User Group
 12 Welcome
 45 Domo News
 10.3K Using Domo
 2K Dataflows
 2.6K Card Building
 2.3K Ideas Exchange
 1.3K Connectors
 356 Workbench
 272 Domo Best Practices
 14 Domo Certification
 484 Domo Developer
 58 Domo Everywhere
 114 Apps
 736 New to Domo
 86 Dojo
 Domopalooza
 1.1K ζ₯ζ¬ζ―ι¨
 4 ιε ΄ζ₯ζ¬ζ―ι¨γΈγγγγ
 32 γη₯γγ
 65 Kowaza
 305 δ»²ιγ«ηΈθ«
 668 γ²γγγε ±ζ