I've really been struggling to get my head around version 6 table calculations. The thing in particular which has had me confused is the definition of the windows that the calculations work over and how the various places in the user interface where that can be set interact with each other.
Unless I've missed something, this isn't an area that has had much coverage in the beta documentation - I'm sure it will be covered well in the final v6 documentation, though. In the meantime, I thought I'd pass on a little example I put together which helped me understand it better - plus a few of the key "aha" moments that generated.
I think I've got this all right, but I'm sure one of the Tableau folk will jump in and correct any misconceptions.
The workbook just uses the standard Coffee Sales data source. I've put a few dimensions on the Columns and Rows shelves and put a calculated field on the text shelf which just returns the value of the INDEX() function - which displays the index of the current row within the window used by the windowing functions. The index value is used extensively by the window functions, so you have to be very clear what it is going to be returning in the context in which the calculation is used.
You can define the properties of the window to be used (which sets the context for the calculation) in three ways:
a) From the "Table Calc Properties" link when editing the definition of the calculated field.
b) From the "Compute Using" menu option from the pill menu for the calculated field on the shelf.
c) From the "Edit Table Calculation..." menu option form the same pill menu.
Experiment with all the options on all of those menus and watch what happens to the values of INDEX(). At first sight when I did that, some of them seemed to make no sense at all. But thinking about it carefully, most things now make perfect sense and a lot of things have fallen into place for me.
I won't try to explain everything I did, but here are a few examples, including a couple of those "aha" moments I had.
Bear in mind that the datasource contains 2 years' worth of monthly sales data. For any given Market/State/Product combination there are either 24 rows or zero rows as shown on the second sheet. (I got myself a bit confused for a while with some of the patterns because I had assumed it would be a bit more sparse than that.)
In the description I refer to the 3 places you can change the calculation properties from as (a), (b) and (c), referring back to the list above.
1) When you open the workbook the calculation is in the default state - "Table (Across)". So the index goes from 1 to 13 on each row.
2) Using (b), change it to "Table (Down)". All columns now range from 1 to 20. Makes sense.
3) Again using (b), change it to "Table (Across then Down)". Now the grid is sparse, only increasing the index for Market/State/Product combinations that exist. No Caffe Latte in Colorado, so skip to Caffe Mocha for number 2. Flick to and fro between the two sheets and you'll see there are 24 records for each combination that gets an index value. Again, this all makes perfect sense.
4) Work through all the other Table and Pane across and down options in (b). All made perfect sense to me immediately.
5) Try (b) "Cell". It's exactly the same as "Table (Down then Across)" in this instance, but I suspect it won't always be. I'm not quite sure what this really means - but let's move on.
6) Try (b) Product. Makes sense.
7) (b) State. Note that numbering restarts for each Market.
8) (b) Market. Huh? What's going on? They're all 1...
If you now choose (c) (Edit Table Calculation...), the description explains what's happening:
"Results are computed along Market for each Product, State."
But because Markets are a collection of States, there can only ever be one Market for any State. So the index has to be 1.
9) Now put the pill back to the default by selecting (b), "Table (across)". All rows should now go from 1 to 13.
10) Now let's see what option a) does. Edit the table calculation for Index and select (a) ("Table Calc Properties"). Change "Compute using" from "Automatic" to "State" and save the calculation.
Nothing happened. Huh? What's going on? I just told it to calculate by State.
Brief pause while I bang my head against the wall again.
Now drag the Index field off the Text shelf. Drag it on again. That's better. Now it calculates by State.
So unlike everything else you do when you edit a calculated field, changing it's table calc properties doesn't affect the behaviour of instances of the field that are already in use on the view - just the defaults used when you first add it to the view. (I'm sure one of the Tableau folk will correct me if I've got this wrong, but that's what seems to happen.)
I'm actually a bit embarrassed to admit that it's taken me 2 months to figure this out - but in the interests of saving other people's walls from getting head-shaped indentations like my study I thought I'd pass it on. I think this needs to be made clear in the user interface. I'd vote for text on the window where you set this saying that these are the default properties used when a field is first added to a view and possibly a warning message if you change the properties of a field that is already on a view saying that the properties of existing instances won't be affected.
Now that we've got that clear, we can start exploring the really interesting stuff. Using option (c) from the pill on the Text shelf explore all the Advanced options. I won't try explaining all of those - partly because I still don't fully understand and partly because there are so many permutations. Here's just one example:
11) Using (c), select Advanced, pick Product and State in "Compute Using" and set "Order Along" to Type, Minimum. OK on that and the description says:
"Results are computed along Product, State (sorted ascending by Min. Type) for each Market."
Now the indexes jump around all over the place, as you would expect.
I still get myself into states where the calculated field gets a red exclamation mark and I'm not quite sure why what I've selected is invalid - but this has really helped me to get clearer what is going on.
Hopefully this will save others from some of the pain I've been having.
Table Calc Windows_0.twbx 197.3 K