9 Replies Latest reply on Mar 6, 2019 10:52 AM by Derek Wolff

    Calculating moving averages beyond window

    David Mendel

      Does anyone know how to calculate moving average on all the data, not just that shown in the window?


      For instance, I want to show the 12 month moving average of something for only the past 3 months.


      Using WINDOW_AVG( SUM([Quantity]),-11,0 ) doesn't work when you filter out everything but the last three periods, as there are not 12 periods in the window on which to calculate average (yeah, I know, that's why it's called "Window Average"). So, at most it's a three month moving average.


      I can make it *appear* correct, by not filtering the data and just hiding all but the last three columns, but that doesn't work for me going forward as the hidden columns don't dynamically change. This will sit on a Tablaeu Server and I need it to shown only the past three months of data, regardless of when someone views it.