(Note to self: V10.0 workbook)
See attached. In your original sheet I added a calc called INDEX. This is a helpful trick to let you see what Tableau is doing with the index and how it's walking the table. I set it so that it restarts every [Number]. And then I added a calc that compares INDEX to the window max of INDEX, again, restarting every NUMBER.
You can see the values in the first sheet.
Once I had it so that I could identify the last entry for each NUMBER, I made a duplicate of the sheet, moved [Calculation2] to filters and filtered on Value = 1. I removed INDEX from TEXT.
Also, I right-clicked on the [Service] pill and unchecked "show header". That eliminates the column entirely, but it still stays on the sheet.
KathyAndy Report A.twbx 456.7 KB
Worked like a charm; exactly what I needed. I don't understand how, yet, but I'm going to go study those calcs now.
Just a little guidance on what's happening under the hood in there...
Tableau can "walk" a table lots of different ways. Usually, by default, it either works "down" or "across" when evaluating table calcs. When you rebuild what I did, you will notice that tableau puts [index] on the sheet and defaults it to TABLE(down). So if [index] is on the text shelf, you will essentially see it number the rows 1 through N down the table. I like to use an [index] calc to make sure I understand what tableau is doing, and what my instructions to tableau make the software do. It's something worth putting in your bag of tricks once you get this figured out.
So the settings I used for [index] to get it to count 1-N for each chunk of data was to specify the dimensions, so that it walks through the dimensions in the order I want it (in this case, [number], and within NUMBER cycle through [Service].) And then I told it to restart for each [Number]. So we get 1-N for each number. It doesn't matter if there is one row or 100. Always start at 1 for each [Number].
And then I did a WINDOW_MAX([index]) in the filter calc. I also did the same settings on that table calc. Now that calc says, "If my current [index] is equal to the MAX for this [Number] (remember, it restarts for each number), then I'm at the last one. So remember this one by setting my value to 1." You can see that on the first sheet.
Now the second sheet. The really cool thing about using a table calc as a filter is that it doesn't actually discard the other rows, like a quick calc does. It leaves the whole underlying table intact. It just DISPLAYS what satisfies the filter. So when I put it on the filter shelf, I selected for value = 1, and that will only display the rows that are the last one for each [number]. All the other rows are still lurking in the table that supports this sheet. (Otherwise that PREVIOUS_VALUE calc you are using would have crapped out!)
Table calcs. They can be your worst enemy or your best friend.
Right click on the [Service] pill on the ROWS shelf. There is an option for "Show Header". By default it gets set to ON. You can click it off, and the whole column "hides" (but still does its job on the sheet.)
Whoa! Look at that! I usually hid the header from the viz itself, never tried it from the pill. Thanks again!