# Rolling 12 Month Avg

Hi all - Currently I have a dataset that contains a date (by quarters), brand, sub-segment, 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.

• Indiana 🥷

Have you read my prior article on rolling averages using window functions? This may help you out: https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages#latest

**Did this solve your problem? Accept it as a solution!**
• Indiana 🥷

How far back does your data go? If you don't have any data prior to 2020-Q4 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)
```
**Did this solve your problem? Accept it as a solution!**