5 Replies Latest reply on Jan 6, 2013 1:32 PM by Alex Kerin

    Change level of detail depending on filter

    Eric Huhn

      I have a dashboard with a map (being used as a filter) and a corresponding bar chart.  Let's say my underlying data has several states and each state has 3 - 10 cities per state.  What I'd like to do is that if I've selected all or multiple states, then the bar chart should be at the state level.  But if I've filtered to a single state, then the bar chart should be at the city level.

       

      I thought I could create a calculated field like:

       

      IF COUNTD([State]) = 1 THEN [City] ELSE [State] END

       

      but it says I can't combine aggregates and non-aggregates.  Is there a workaround for this?  Thanks.

        • 1. Re: Change level of detail depending on filter
          Alex Kerin

          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.

          • 2. Re: Change level of detail depending on filter
            Alex Kerin

            Okay, I've done it here - I needed City on the level of detail. This makes the partitioning a little more complex.

            • 3. Re: Change level of detail depending on filter
              Eric Huhn

              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.

              • 4. Re: Change level of detail depending on filter
                Toby Erkson

                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

                 

                Thanks.

                • 5. Re: Change level of detail depending on filter
                  Alex Kerin

                  Of course.

                   

                  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:

                   

                  td.png

                  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.

                   

                  right.png

                   

                  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.