Rolling Average Calculation

TimManders

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.

zcameron

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!

zcameron

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!

zcameron

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

TimManders

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.

Unknown

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!

user01384

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?

zcameron

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!

JR_S1L

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?

jaeW_at_Onyx

https://dojo.domo.com/discussion/54637/rolling-average-for-each-category

https://www.youtube.com/watch?v=cnc6gMKZ9R8