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

# Calculating moving averages beyond window

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.

Thanks,
David

• ###### 1. Re: Calculating moving averages beyond window

Because you want to filter after table calculations have been evaluated, you want to filter on a table calculation pill so the filter is applied after table calculations are evaluated.

a formula like:

LOOKUP(MIN([Date]),0)

and then if you right-click the field in the list of Measures, and from the context menu select, Convert to Continuons, you can place a pill for that field on the Filter shelf and setup your Relative date filter as before, and get the results you are looking for.

example in the attached.

4 of 4 people found this helpful
• ###### 2. Re: Calculating moving averages beyond window

Bingo! That's what I needed.

Thank you Joe.

• ###### 3. Re: Calculating moving averages beyond window

David & Joe:

It looks like you could have used what I call the "native" moving average calc to get the same result as David's custom calc [  WINDOW_AVG( SUM([Quantity]),-11,0 ) ]

Then we can apply Joe's custom date calc as a filter to achieve the same result.

So ... My question:

Is there a reason to use a custom calc as David did over the "native" calc? Or is this just a matter of personal choice?

------------

By way of context ...

I used the native moving average calc, set to show an 11 month moving average and get the identical results as David's calc. See screen shot.

One benefit of using the native calc is that you can show only months when there is a full 11 months of prior data to average. Makes it clear when you have a full 11 month moving average. (A nice data viz tip from Joe)

Here's what that looks like, followed by how to do it.

How to do it. (Note that the dialog box shows I selected a 12 month moving average. I later changed it to 11 month to match David's custom calc.)

I also attach a modification of Joe's workbook.

• ###### 4. Re: Calculating moving averages beyond window

As for the choice of Quick Table Calculation or Custom Table Calculation, to me, the choice is personal, both produce the same result, but with a different interface. I use both, the main factor is context.

The option "Null if there are not enough values" is available in the Edit Table Calculation dialog in both routes.

• ###### 5. Re: Calculating moving averages beyond window

Great! Thanks for the info Joe.

• ###### 6. Re: Calculating moving averages beyond window

I just used this solution to fix a similar issue that I had. It worked great, but now I no longer have the option to apply this filter to all sheets using this filter even though it is from the same data source. Can you let me know if it is possible to have the functionality of calculating the avg using all values and allowing it to apply to all sheets?

Thanks,

Yigael

• ###### 7. Re: Calculating moving averages beyond window

I also have the same question as Yigael.

• ###### 8. Re: Calculating moving averages beyond window

I'm not sure if I have the same question as Yigael, but I need the starting date to be dynamic so whoever is looking at my VIZ can change the time frame should they choose. Any thoughts on how to accomplish this?

• ###### 9. Re: Calculating moving averages beyond window

Hi Joe -- This solution works great -- thanks for the post. Is there a way to apply this filter to multiple sheets? Or a similar technique that would allow this type of date filter that can be applied to multiple sheets?

Thanks