Not straightforward. size() will give you the number of states showing (when the partitioning is set correctly), but I cannot figure how to then show the cities.
Wow, don't know that I would have gotten there on my own. Thanks so much.
I'm assuming that it was necessary to create a hierarchy for State and City. I couldn't get it to work till I noticed you had done that. Also wouldn't have gone for ATTR and LOOKUP as part of the solution.
Ditto. Alex, can you explain how the statement you came up with works? It's always helpful in understanding since some commands lack good documentation or aren't intuitive enough.
if size()=1 then lookup(attr([City]),0) else "All" end
You don't need a hierarchy - at least for my example - you can drag City out of the hierarchy and it still works.
You actually don't need the lookup either - that was a legacy from earlier.This works fine:
if size()=1 then attr([City]) else "All" end
In essence we are looking at the size of the partition. In the way that we set the partitioning we are looking at the number of states in the view (I'll come to that later). If it's one then display the City, if more than one, then All.
We need the attr in there because you can't combine a table calculation with a row level piece of information (City). It's hard to explain attr, but Richard Leeke does a good job here: http://community.tableau.com/message/195761#195761. We could have used max(), min() to the same effect.
We need City on the level of detail because otherwise the individual cities are not displayed - either the attr results in a * (multiple results for our attr call), or if using min() then the first city in the alphabet.
Now the partitioning. This is trickier than normal. See the attached second sheet. I have 5 copies of exactly the same calculation: size() but each with different partitioning. The first is with the partitioning set to Table Down:
This means the partition is the entire table so we get a count of every city (1,616 of them)
The next is set to compute using city. I've always found this a little counter intuitive, but this means count the cities however they are broken up by other discrete pills. As the only other discrete pill is State (blue table calculation pills do not partition other table calcs) we now get a count of the number of cities in each state.
The next is set to compute using State. Count the states as broken up by the cities. In practice this means count the number of cities with the same name across all of the states (there are 4 Auburns in 4 states in this data set)
Finally we have the right answer. We need to set an advanced partitioning where we set a level that the partitioning works at. This is a good explanation of that: http://community.tableau.com/thread/117817. We kind of set it to table down to look across the whole partition, but count the number of states. Note the order of City, State in that right dialog box is critical. If it's the other way round then we get a count of the number of cities again.
That's really it. I'm sure there are better ways of describing the partitioning, and that my wording may be off. Hopefully you get the essence though.
hide city with size.twbx.zip 436.5 KB