7 Replies Latest reply on Aug 6, 2012 6:23 AM by Steve Martin

    Need to show top 25%, average and bottom 25% on a stacked chart

    Steve Martin

      Please accept my apologies in advance, been out of Tableau development for 10 months and have forgotten a lot, now find myself faced with a can of worms and a three-day deadline argh!

       

      Basically, I just need a horizontal stacked chart that enables the end user to select the customer in question using the radio-button quick-filter for which they shall then be represented against the top 25%, average and bottom 25% of all the other customers displayed as the four marks (see mocked pick)

       

      Unfortunately, to get my if statement to work, I have had to de-aggregate my measures and with just the selected customer, I cannot remember even the basics (it shall all come flooding back soon but spending soo much time doing complex querying and making Excel vba to Oracle user tools has knocked me for six); result is, I cannot remember if I'm going in the right direction or not.

       

      Please help, I in the meantime shall sit here with my nose pressed against both the fundamentals and advanced coursebooks from last Summer...

       

      Steve

        • 1. Re: Need to show top 25%, average and bottom 25% on a stacked chart
          Steve Martin

          Anyone able to help on this?

          To make it easier to understand, I am looking for as dimensions, the sum of the top 25%, the sum of the average and the sum of the bottom 25% of my measure to be able to use in line with my selected customer, I am thinking that the three dimensions (top, average & bottom) would be best as sets.

           

          Steve

          • 2. Re: Need to show top 25%, average and bottom 25% on a stacked chart
            Jonathan Drummey

            Hi Steve,

             

            In your first message you had said you had a 3 day deadline and that's passed, are you still looking for help on this?

             

            Here are a couple of posts on doing similar kinds of grouping:

            http://community.tableau.com/thread/109093

            http://community.tableau.com/thread/114541

             

            Jonathan

            • 3. Re: Need to show top 25%, average and bottom 25% on a stacked chart
              Steve Martin

              Hi Jonathan,

               

              I've managed to get my deadline extended to accommodate this; those posts are part the way there, what I have so far is two sets - Top 25 and bottom 25, which I am still trying to work out how to get them to be the top 25% of my measure and bottom 25% of my measure.

               

              I have created four stacked bar charts: selectable (by single selection), sum of Top 25 using the set as the filter, the same for the bottom and then something similar for the average then moved them as close as possible on the dashboard.

               

              I am finding that trying to place them together on the one chart messes up the partitioning and kills the charts.

               

              So if you know of a way around this that shall allow me to achieve this I shall be most grateful.

               

              I've attached both what I have so far, and what I am trying to achieve as the Excel piece I put together as the poc this one in particular needs to look like that of the orange tabs though I shall soon need to achieve the that of the purple tabs which I think your links my be able to achieve.

              • 4. Re: Need to show top 25%, average and bottom 25% on a stacked chart
                Jonathan Drummey

                Hi Steve,

                 

                I've had limited time to go at this and I'm not totally happy with the results, maybe Joe Mako can jump in.

                 

                I don't think Sets are the answer to create separate worksheets because you need to generate a measure (I wasn't clear on what that was, I created a % Satisfied measure), then you need to filter for the top 25%/bottom 25%/middle rows based on that measure. I came up with a couple of options:

                 

                The first option was based on the "top and bottom calc show 2nd calc jm edit" attached workbook from an old forum post that Joe and I worked on several months back - unfortunately, I can't find the original post, through the Tableau forum search or Google.

                 

                The "top and bottom 25 percent" workbook uses your initial workbook with a duplicated data source (so the calculated fields wouldn't get too confusing) and a set of table calculations to identify the top/bottom/middle, and to create measures for the % Satisfied/Disatisfied/Neutral in each, so you can end up with all three stacked bars in one graph. The problem here is that it takes 3+ minutes to refresh the view on my machine. There are definitely some optimizations that can happen, and I was running into a strange Tableau error that I might be finally able to replicate where adding a table calculation to the view was changing the results of other calculations. In any case, I ran out of time to make this work. You can see the two worksheets in Table Calc Workout and Table Calc View. Even if optimizations are done, it still might not be fast enough to be useful.

                 

                The alternative is along the lines of what you were creating, where there is one bar per top/bottom/middle. I used some of the calcs from above to do that for the top 25%, you can see that in the Single Chart sheet. You'd need to duplicate the calculations and adjust the filters to create additional worksheets to include in the final view.

                 

                In any case, I imagine that you'll be better off keeping the chosen field as a separate worksheet. That way when a user refreshes the dashboard only that worksheet needs to change, and not the totals worksheet(s).

                 

                Finally, the performance issues would entirely go away if you pre-calculated the initial measure (i.e. % Satisfied) and the position/percentile in SQL. I see you are using an extract, so the extra computation time while creating the extract shouldn't be an issue.

                 

                Also, I used a different color scheme than the one in the Excel wookbook, which used red/green/blue. Since ~10% of males are color-blind, it's not good practice to use red and green in the same view with no other differentiating factors like shape/angle/size.

                 

                Let me know if you have any questions,

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Need to show top 25%, average and bottom 25% on a stacked chart
                  Steve Martin

                  Cripes! I am rustier than I first thought.
                  This is a brilliant effort - it still takes around 2.5 mins on my Core i7 with 8GB so going back to your previous point that breaking them down to 4 different charts may just be the way forward.

                   

                  I do agree that the separate charts idea is far better performance wise, I provided just 10% of 1 months data for uploading, we are seeing circa 900k rows per month the only benefit being that Tableau was my choice so I now need to deliver in order to convince that Tableau Server would also be a good idea.

                   

                  I would really like to see what Joe, Richard or Andy could come up with though I really do doubt that your efforts could be topped

                   

                  On a side note I must admit, despite using window calcs regularly at server level, I've always avoided them in Tableau simply as I've never really understood how they work despite taking both the fundamentals & advanced courses last year [just never got it I suppose...]; my alternative method involves creating new connections to handle the top and bottom 25%, only this would likely slow everything down especially as I have an initial 5 measures to have to handle now [images, floorplans, virtual tours, two others I cannot remember atm...],; my method which does get the top/bottom 25% is:

                   

                  Top 25:

                  With toprank As (

                  Select

                  m.*

                  ,Dense_Rank() Over(Order By images Desc) As rank_of_images

                  From mediabenchmark m

                  )

                  Select *

                  From toprank

                    Where rank_of_images <= (

                      Select Round((Count(*)/4)*1,0) From toprank

                  );

                   

                  Bottom 25:

                  With bottomrank As (

                  Select

                  m.*

                  ,Dense_Rank() Over(Order By images) As rank_of_images

                  From mediabenchmark m

                  )

                    Select *  From

                  (Select * From bottomrank Order By RowNum Desc) bottomrank

                    Where Rownum <= (

                      Select Round((Count(*)/4)*1,0) From bottomrank

                  )

                  Order By RowNum Desc;

                  • 6. Re: Need to show top 25%, average and bottom 25% on a stacked chart
                    Joe Mako

                    Attached is my first pass. I am not sure if I understand the exact computation that you want, but I am sure anything that you want can be achieved.

                     

                    You can get all four stacked bars in a single sheet with a refresh rate of less than 10 seconds.

                     

                    It uses a few tricks, so it is likely not an intutive approach.

                     

                    You are welcome to contact me via the email in my profile to set a screen sharing session if you want to dive into this approach, or adjust it to fit your exact needs.

                    • 7. Re: Need to show top 25%, average and bottom 25% on a stacked chart
                      Steve Martin

                      Apologies for the lateness, been away with whooping-cough...

                       

                      I said it in my previous post that I didn't think either you Richard or Andy could better Jon, but I think you may have just improved on it especially in just one pass...

                       

                      I shall take some time this evening to work out how you did it, but many thanks Joe anyway.