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:
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.
12MoMovAvg jm edit.twbx.zip 28.4 KB
Bingo! That's what I needed.
Thank you Joe.
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.
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.
Great! Thanks for the info Joe.
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?
I also have the same question as Yigael.
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?
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?