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.
Thanks in advance and pls let me know if you need anymore info :)