I am trying to create a rolling mean or average.
For example, the 1st point would be based on 1 mark, the 2nd on 2, 3rd on 3, etc.
Does anyone have an example?
I think I could use a CMLTOTAL / RANK but I am not sure how do get the RANK line up so I am looking for an example first.
Hi Shawn,
This link will help you further: https://www.dundas.com/support/learning/documentation/analyze-data/formulas/standard/moving-average
You can use MOVAVG($measure_name$, N) in a formula measure. There are other variations of moving averages supported too like weighted moving averages - you can find all references in our support site.
Hi Shawn,
Since you are trying to do a moving avg of a cumulative total you could also do this in your query using CTEs.
Take this example of the 50 most recent BMIS:
with CTE AS
(select top 50 vs.date, vs.BMI
from vital_signs as vs
where bmi is not null
order by vs.date desc),
CTE2 AS (
Select *,
SUM(BMI) over(order by vs.date desc) Cumulative_Total
from CTE)
Select *,
Avg(Sum(Cumulative_Total)) Over (Order By vs.date desc Rows Between 49 Preceding and Current row) as ‘Cumulative Moving Avg’
from CTE2
group by vs.date, BMI, Cumulative_Total
order by vs.date desc
Derek