how to calculate rolling 12 months average for 3 years

Hi, I am trying to calculate rolling 12 months average for 3 years for every ID and my result is not what i am expecting. Below is the example of my dataset. --drop table #table1 create table...

rodgerkong

Aug 30, 2024

unicorn_19

I made some change with your code, check the result, is that what your want?

SELECT ID, MonthYear,
  AVG(netvalue_1) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ,
  AVG(netvalue_2) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ,
  AVG(netvalue_3) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM #table1
ORDER BY ID, RIGHT(MonthYear, 4) + LEFT(MonthYear, 3)

rodgerkong

Iron Contributor

Aug 30, 2024

unicorn_19

I made some change with your code, check the result, is that what your want?

SELECT ID, MonthYear,
  AVG(netvalue_1) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ,
  AVG(netvalue_2) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ,
  AVG(netvalue_3) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM #table1
ORDER BY ID, RIGHT(MonthYear, 4) + LEFT(MonthYear, 3)

unicorn_19
Copper Contributor
Aug 31, 2024
here avg works and also sorting. but i wanted 12 months rolling and it will be 11 preceding and current row

Forum Discussion

how to calculate rolling 12 months average for 3 years

Resources