Rolling 12 Month Avg
Hi all  Currently I have a dataset that contains a date (by quarters), brand, subsegment, sample type, and a NPS column. Using a Beastmode calculation, I take the favorable responses and divide it by the total number of responses to get a NPS score. This data is granular (many NPS respondents, by brand, by segment per quarter).
I load this dataset in Domo, apply necessary filters, and I am able to produce a graph using the Beastmode calculation that shows the NPS score by quarter. Using this Beastmode calculation, I am able to slice and dice the data.
NPS score Beastmode calculation:
count(case when `T2B` = 'Yes' then 1 end) / count(case when `Month_Year` is not null then 1 end)
The problem I'm trying to tackle is that the NPS score should be a rolling 12 month average (past 4 quarters).
Ideally, it would be nice to resolve my issue using Beastmode, but I'm open to any other options! I can foresee if an ETL is used, some of this flexibility would be eliminated as things would now be hardcoded.
Thanks in advance and pls let me know if you need anymore info :)
Best Answers

Hi @DomoNewbie
Have you read my prior article on rolling averages using window functions? This may help you out: https://dojo.domo.com/discussion/52679/domoideasconferencebeastmodesrollingaverages#latest
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
How far back does your data go? If you don't have any data prior to 2020Q4 it'll return NULL because there are no prior rows for it to pull from. When you have a NULL with a simple arithmetic operator (=, , *, /) the entire result ends up being NULL. You can wrap your entire LAG...OVER() in a COALESCE function and coalesce it with 0 to force NULLs to be 0
COALESCE(lag(count(... over (order by `Quarter`), 0)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Answers

Hi @DomoNewbie
Have you read my prior article on rolling averages using window functions? This may help you out: https://dojo.domo.com/discussion/52679/domoideasconferencebeastmodesrollingaverages#latest
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Hi Grant  I read your post, modified the formula found in the linked post to suit my needs:
Which yielded the below results. Awesome. Exactly what I needed! Thanks for your guidance and quick response!
Do you happen to know why the quarters with less than 4 scores do not have any rolling avgs? Thanks again!
0 
How far back does your data go? If you don't have any data prior to 2020Q4 it'll return NULL because there are no prior rows for it to pull from. When you have a NULL with a simple arithmetic operator (=, , *, /) the entire result ends up being NULL. You can wrap your entire LAG...OVER() in a COALESCE function and coalesce it with 0 to force NULLs to be 0
COALESCE(lag(count(... over (order by `Quarter`), 0)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 
Perfect. Your response makes sense. I only loaded 5 quarters worth of data into Domo which explains why the formula returns NULL. I was thinking that the remaining quarters that had data still would be able to divide by 4, but the previous month's NULL errored out the result.
Thanks again for your expertise and responsiveness 😀
0
Categories
 All Categories
 1.6K Product Ideas
 1.6K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 288 Workbench
 4 Cloud Amplifier
 7 Federated
 2.8K Transform
 94 SQL DataFlows
 593 Datasets
 2.1K Magic ETL
 3.6K Visualize
 2.4K Charting
 667 Beast Mode
 42 App Studio
 34 Variables
 644 Automate
 165 Apps
 434 APIs & Domo Developer
 42 Workflows
 3 DomoAI
 31 Predict
 12 Jupyter Workspaces
 19 R & Python Tiles
 378 Distribute
 107 Domo Everywhere
 266 Scheduled Reports
 5 Software Integrations
 109 Manage
 106 Governance & Security
 8 Domo University
 23 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 95 Community Announcements
 4.8K Archive