# Rolling Average Calculation

Member

I have a set of monthly data and would like to calculate a 3-month rolling average. For example:

Jan = 30

Feb = 32

Mar = 34

April = 36

Rolling average for March would be 32 ((30+32+34)/3), for April would be 34 ((32+34+36)/3).  It is probably something fairly simple, but I'd appreciate your help.

Thank you.

## Best Answer

• Domo Employee
Answer ✓

A Beast Mode is calculated on either a single record or group of records, depending on how the data on the card is being grouped. Because of that, the calculation doesn't have a way of reference other records. For example, I can't say, "I'm looking for the April average. Look back at other records that come before April and sum them up for me." You could create a card that does a quarterly average, though. To do that, set your date grain to "by Quarter" at the top of the card, then add your date and value columns to the X and Y axis, respectively. Then set your aggregation option for your value column to AVG.

Alternatively, you'd need to do the calcuation you're looking for in a Dataflow.  The transform would look something like this:

SELECT
`DateField`
,`ValueField`
,(SELECT
SUM(`ValueField`) / COUNT(*)
FROM
TABLE t2
WHERE
t2.`DateField` > LAST_DAY(DATE_SUB(t1.`DateField`, INTERVAL 3 MONTH))
AND t2.`DateField` < LAST_DAY(t1.`DateField`) ) AS `Rolling_Average`

FROM
TABLE t1

This transform assumes that the DateField contains a date representing the month, e.g. "2015-01-01" for January.

We're using a subquery to go grab the sum of the last 3 months worth of values and then dividing them by the number of records it found in that range and calling that the Rolling_Average field. You'd need to replace your field names for `DateField` and `ValueField` (and add any others you want in the resulting dataset) and replace TABLE with the dataset table you're using as your input.

I hope that helps!

## Answers

• Domo Employee
Answer ✓

A Beast Mode is calculated on either a single record or group of records, depending on how the data on the card is being grouped. Because of that, the calculation doesn't have a way of reference other records. For example, I can't say, "I'm looking for the April average. Look back at other records that come before April and sum them up for me." You could create a card that does a quarterly average, though. To do that, set your date grain to "by Quarter" at the top of the card, then add your date and value columns to the X and Y axis, respectively. Then set your aggregation option for your value column to AVG.

Alternatively, you'd need to do the calcuation you're looking for in a Dataflow.  The transform would look something like this:

SELECT
`DateField`
,`ValueField`
,(SELECT
SUM(`ValueField`) / COUNT(*)
FROM
TABLE t2
WHERE
t2.`DateField` > LAST_DAY(DATE_SUB(t1.`DateField`, INTERVAL 3 MONTH))
AND t2.`DateField` < LAST_DAY(t1.`DateField`) ) AS `Rolling_Average`

FROM
TABLE t1

This transform assumes that the DateField contains a date representing the month, e.g. "2015-01-01" for January.

We're using a subquery to go grab the sum of the last 3 months worth of values and then dividing them by the number of records it found in that range and calling that the Rolling_Average field. You'd need to replace your field names for `DateField` and `ValueField` (and add any others you want in the resulting dataset) and replace TABLE with the dataset table you're using as your input.

I hope that helps!

• Domo Employee

Tim, were you able to try out the solution I proposed? Did it work for you?

• Member

Thank you. I have not been able to test this yet, but the logic makes perfect sense, both about how to build cards with appropriate date grains, and that DataFlow is probably the best option for flexibility.

• Hi zcameron,

I'm trying to accomplish the same rolling total but not for a 3-month interval. Instead, I need to sum the last 13 periods (each year is divided into 13 fiscal periods) for each period on the chart. So for example, when the x axis is showing Period 13 in 2016 then the y axis value should be the total value of Period 1 through Period 13 in 2016. Any help is appreciated! Thanks!

• Member

Welp, it's 3 years later and I'm having a similar issue.

I'd like to perform a weighted rolling average (say ROI), based on an underlying calculation.  So, for example,

 MON Cost Sales ROI Jan 100 200 2 Feb 100 250 2.5 Mar 150 250 1.666667 Apr 250 275 1.1 May 100 150 1.5 Jun 100 90 0.9

window: 0, -3

 Mon Rolling ROI Jan 2 Feb 2.25 Mar 2 Apr 1.55 May 1.35 Jun 1.144444

Am I right that the only solution available is through a custom query to the db?  Does Domo not have a built in method for this type of thing?  I'm not sure I'd consider a rolling average 'advanced analytics', so just seeing whether Domo has an option here other than going back to the database (rather than using a domo supported feature)

My big concern is that this isn't necessarily very scalability using a custom query for each and every level of detail in a dashboard.  Quarterly window?  that's another query.  Yearly window?  That's another query.  Daily window? that's another query.  Am I missing something here?  Maybe this is on the roadmap or a feature request somewhere?

• Domo Employee

The supported methods of accomplishing a rolling average are handled in the ETL layer of Domo. Here's a link to the Help Center Article that describes how to accomplish it in MySQL, Redshift, and Magic ETL.

http://knowledge.domo.com/?cid=createrollingaverage

I hope that helps!

• Member

I am attempting finding the rolling average of a dataset containing units, date, and name. There are multiple units per name per date.

How do I calculate the rolling average for each name?

• Coach
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"