Rolling Average Calculation
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.
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:
SUM(`ValueField`) / COUNT(*)
t2.`DateField` > LAST_DAY(DATE_SUB(t1.`DateField`, INTERVAL 3 MONTH))
AND t2.`DateField` < LAST_DAY(t1.`DateField`) ) AS `Rolling_Average`
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!2
Tim, were you able to try out the solution I proposed? Did it work for you?0
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.0
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!0
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?0
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.
I hope that helps!0
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?0
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
- 7.7K All Categories
- 12 Connect
- 925 Connectors
- 247 Workbench
- 441 Transform
- 1.7K Magic ETL
- 61 SQL DataFlows
- 456 Datasets
- 88 Visualize
- 222 Beast Mode
- 2.1K Charting
- 8 Variables
- 35 Cards, Dashboards, Stories
- 5 Automate
- 349 APIs & Domo Developer
- 85 Apps
- 17 Predict
- 3 Jupyter Workspaces
- 14 R & Python Tiles
- 242 Distribute
- 60 Domo Everywhere
- 241 Scheduled Reports
- 18 Manage
- 39 Governance & Security
- 46 Product Ideas
- 1.1K Ideas Exchange
- 6 Community Forums
- 19 Getting Started
- 6 Community Member Introductions
- 50 Community News
- 18 Event Recordings
- 577 日本支部