9 Replies Latest reply on Jan 20, 2015 9:51 AM by Jonathan Drummey

    Rank by [Store] with Stacked Bar Chart

    ryan.richmond

      Hi, I'm having trouble getting rank to perform as expected when using a stacked bar chart. The goal here is to show the rank for the total number of sales by store but have the sale type broken apart with color. I've attached a sample tableau workbook as well. In one sheet the rank is correct and the other sheet the color is correct. I just can't seem to merge them. Any help is greatly appreciated.

       

      StorePet Type# of Pets
      CaliforniaDog4
      CaliforniaCat6
      CaliforniaFish1
      TexasDog2
      TexasCat3
      FloridaFish1
      FloridaDog2
      FloridaCat3
      New MexicoFish1
      New MexicoDog2
      New MexicoCat4
      ArizonaFish5
        • 1. Re: Rank by [Store] with Stacked Bar Chart
          Aman Lamba

          Hi Ryan,

           

          Is this what you are looking for?

           

          Hope it helps!

          • 2. Re: Re: Rank by [Store] with Stacked Bar Chart
            ryan.richmond

            Aman,

             

            Thanks for the response. Yes that is exactly what I was looking for however it seems the sample data I supplied was not enough to replicate the issue I was having. I've attached a new workbook and sample data. As you can see I replicated exactly what you did but I still don't get the desired results.

             

            Demo.JPG.jpg

            • 3. Re: Rank by [Store] with Stacked Bar Chart
              Matt Lutton

              Untitled.png

              I believe I've found a solution, as long as you have 8.1 installed.  I am not sure a solution exists, using the INDEX calculation alone -- there is a reference to Dense Rank in the TCRL: Dense Rank

               

              The attached uses the RANK_DENSE calculation on a TOTAL(Sum(Sold)) field so each stacked bar is evaluated based on the Total Sales across all Item Types in that Location.  I realized I had to use RANK_DENSE, when the RANK calculation was returning results like 1, 4, 6, etc (based on the # of item types in each location).  RANK_DENSE ensures we get consecutive values in each row.  Very glad I got interested in this problem and didn't give up, as I now have a better understanding of the various RANK types in 8.1

               

              The compute using on the Total Sold calc is: Addressing on Location, then Item Type, restarting each location.  The compute using on the Rank calc is set to address on Location, then Item Type, with no restarting value.

               

              I hope this helps--it took me over an hour to get to this solution, but I knew there had to be a way!

               

              Jonathan Drummey - Is there anything that would make this easier, or anything you'd do differently here?  Just curious, if you have the time to respond.  I would've given up on this long ago if it wasn't for your inspirational work and constant help.

              3 of 3 people found this helpful
              • 4. Re: Rank by [Store] with Stacked Bar Chart
                ryan.richmond

                Matthew,

                 

                That did the trick. I can't believe how complicated the solution was for something that I thought was simple. I spent more time than I care to admit on this particular request. Regardless thank you for your help!

                • 5. Re: Rank by [Store] with Stacked Bar Chart
                  Matt Lutton

                  That is part of the fun (and/or "challenge") with using/learning Tableau - it is very satisfying to create a solution, but its just not always easy to learn and understand Tableau without help; it definitely takes practice, experience, and user feedback to learn this tool (and an understanding of some basic data concepts)--and I could do more to get feedback from more experienced users.  The good news is that there are more resources than ever to help us with that.

                  • 6. Re: Re: Rank by [Store] with Stacked Bar Chart
                    Jonathan Drummey

                    Hi Matthew,

                     

                    You just ran into one of the archetypal uses for At the Level. With the Location Sorted by Sum(Sold)/Desc, we can use the INDEX() calculation with Compute Using of Location, Item Type, At the Level Item Type and get the desired results:

                     

                    2014-03-25 16_00_36-Tableau - Rank Dense Stacked Bar Chart ML.png

                     

                    I know a couple weeks ago you'd asked for a deeper description of At the Level, I've got one mostly written up (probably next week before it comes out). The key idea here is that with At the Level, the Index() is incrementing on each location and returning the same result to every Item Type within that location.

                     

                    Jonathan

                    • 7. Re: Re: Rank by [Store] with Stacked Bar Chart
                      Matt Lutton

                      Awesome, thanks, I really do appreciate this.  I thought I had tried using At the Level, but must have never gotten it quite right.  I am just struggling with how to predict/know in advance what the best approach is.  It seems the only thing that works for me is practice and experience -- and asking others for help/advice!

                      • 8. Re: Rank by [Store] with Stacked Bar Chart
                        riccarda.dmke

                        Hi Jonathan,

                        hi Matthew,

                         

                        after desperately looking for an answer to my problem or at least someone who shared it, I tried following your example and got much better results than doing it by myself. However, I have an additionall issue: my ranking includes up to 100 locations and I do want to improve the performance of a dashboard where this ranking is only one of several graphs and limit the default view to only the first 5 by volume. I tried following the steps with above example and additionally to include a quick filter but that does not really do the trick neither does creating sets help as they lead me to exactly the problem that was described initially by Ryan. Any ideas?

                         

                        Thanks a lot for your support / help

                         

                        Riccarda

                        • 9. Re: Rank by [Store] with Stacked Bar Chart
                          Jonathan Drummey

                          Hi Riccarda,

                           

                          You say things aren't helping and not doing the trick, however you haven't specified what the current performance is nor your goal for improvement. You also haven't specified volumes, what kind of data source, whether your hardware is adequately sized for your volumes, etc. And when you say you tried following the steps above and to include a quick filter, without more details we can't know whether what you implemented is what Matthew or I described, nor have you described your data in sufficient detail for us to understand the problem.


                          So we can't answer your question with what you provided. I have two suggestions:

                           

                          1) Run the performance analyzer on your dashboard and get a sense of which worksheets are causing you performance issues. It might not be this one, but another one.

                           

                          2) If you think this worksheet is still an issue, then I suggest posting a packaged workbook (.twbx) with some sample data and then we can take a look.

                           

                          Jonathan