I've attached a workbook with the solution in.
The reason why you were having problems is that the INDEX() function as a table calculation doesn't explicitly happen after the table calculation giving you the moving average.
Thanks to new functionality, you can do what you are looking for with the RANK() function, which allows you to tell tableau what you want to use for the rank.
To make this work I've added two new fields:
1) Added the moving average as a calculated field, so it can be referenced in..
2) Added a RANK function that uses the first calculation.
Yes, these two could be combined into one, but I've left them separate for clarity.
Hope this helps.
Andrew Ball - thank you!
Now - since we're not yet using 8.1 in our Server environment, how might we achieve this in 8.0?
Sorry, I should probably have used an 8.0 workbook to demonstrate my question...
Erm, upgrade to 8.1?
I can't actually think of how you could do this in 8.0 in a easy way whilst staying purely within the view designer. The problem is that you can't tell the table calculation to order by another table calculation, or by data from another source. This means you need to calculated the moving_averages prior to Tableau.
It's not actually too hard to do in this case, but if you need a hand, let me know.
Hint: 2 self joins with an date field incremented by 1 and 2 quarters respectively..
Thanks Andrew - working on getting our Systems folks to upgrade sooner rather than later!