7 Replies Latest reply on Nov 13, 2018 11:38 AM by Yuriy Fal

    Breakout Top 10 Customers in a Stacked Bar Chart?

    Andrew Benito

      Hi,

      First time using the forum, so please bear with me in the explanation of what I'm trying to achieve:

       

      I'm looking at the sum of sales from current 365 days, last 365 days and the previous 365 days in a bar chart, but would like to show the % of those sales that are made up by the Top 10 Customers from each year as stacked bars. (Mockup of what I'm hoping to achieve below). How can I isolate the Top 10 per bar given that each bar is a different measure? Also, if possible I'd like to be able to click on the Top 10 segment of each bar to see the names of the customers either in a tool tip or a legend.

       

      Thank you in advance!

        • 1. Re: Breakout Top 10 Customers in a Stacked Bar Chart?
          Jonathan Drummey

          Hi Andrew,

           

          Just to be clear - when you say that each bar is a different measure then do you mean that each bar (current, last, previous) is a different column?

           

          If so then to get the layout that you want in Tableau at the very least you'll need to either duplicate or pivot the data to have 2 or 3 copies. You can do that in SQL or in the Tableau data source using a union or pivot. (I can do a demo once you answer the previous question).

           

          Jonathan

          • 2. Re: Breakout Top 10 Customers in a Stacked Bar Chart?
            Joe Oppelt

            It'll take several steps to do what you're looking to do.

             

            First you want to make a dimension calc that will break apart the three years.


            Pseudo-code would look like this:

             

             

            If (date range is in the prior 365) THEN ""Previous 365"

            elseif (date range is in the current 365) then "Current 365"

            elseif (date range is in the next 365) then ""Next 365"
            END

             

            (All other rows outside the 3-year span will have NULL here.)

             

            (You don't really specify what constitutes each 365, so that's up to you to decide how that is evaluated.)

             

            Then you'll have a dimension that you can put on columns.  You'll get three columns for the three chunks of dates.

             

            To get the top-10 in each year (I'm assuming that the top-10 will change from year to year) you'll create a rank calc.

             

            RANK(SUM([Sales]), 'desc')

             

            That's a table calc.  You'll need to do settings on the table calc to "Restart Every" and specify the calc dimension you created in the first step.  This will give you a 1-through-N rank within each of those 365 chunks.

             

            Next you'll create a calc that will set a binary value (either 1-or-0, or Y-or-N.  It doesn't really matter what you use for that.)  If the Customer is top-10, then set to 1 else 0.  Again, you'll restart every [calc] you created in step 1.

             

             

             

            Finally you'll want to create a list of all the top-10 customers (those set to 1 in the prior step.)

             

             

            Here are two good links that describe how to do it:

             

            Tableau Public (excellent example from Jonathan Drummey.)

             

            https://kb.tableau.com/articles/HowTo/creating-a-string-list-to-display-in-a-tooltip

            • 3. Re: Breakout Top 10 Customers in a Stacked Bar Chart?
              Andrew Benito

              Hi Jonathan,

              Thanks for the quick response! You are correct: current, last, and previous are all separate columns from the data source.

               

              Example:

               

              Customer     Current 365     Last 365     Previous 365

              Andrew          3,000               2,000               0

              Jonathon          100               500                   700

              Joe                    0                   900               8,000

              Matt                   10                 100               100  

              • 4. Re: Breakout Top 10 Customers in a Stacked Bar Chart?
                Andrew Benito

                Joe,

                I do have the sales broken out by customer per time frame. I'm just confused about how to apply a separate rank filter to each column of data in the bar chart.

                • 5. Re: Breakout Top 10 Customers in a Stacked Bar Chart?
                  Yuriy Fal

                  Hi Andrew,

                   

                  Please find the attached as an example. It is based on the above suggestions by Jonathan and Joe.

                  Screenshot 2018-11-13 at 16.31.09.png

                   

                  Yours,

                  Yuri

                  • 6. Re: Breakout Top 10 Customers in a Stacked Bar Chart?
                    Andrew Benito

                    Hi Yuri,

                    Thanks for doing this, your example is exactly what I'm looking for, but I'm not sure how you achieved it i.e. I can't tell which measures, dimensions, and sets you applied to create the view. Are you able to give me a bit more insight?

                     

                    My data is already parsed into customer by sales for each period of time from the data source so I do not need to calculate the time period buckets like you did:

                     

                    Example:

                    Customer     Current 365     Last 365     Previous 365

                    Andrew          3,000               2,000               0

                    Jonathon          100               500                   700

                    Joe                    0                   900               8,000

                    Matt                   10                 100               100 

                    • 7. Re: Breakout Top 10 Customers in a Stacked Bar Chart?
                      Yuriy Fal

                      Hi Andrew,

                       

                      A recipe would be as follows:

                       

                      1) As per Jonathan's suggestion,

                      you may want to duplicate your datasource table

                      3 times -- one for each of your Measure Columns.

                       

                      This could be done in Tableau using either Pivot or Union,

                      i've used the latter (please, take a look at the Datasource Pane).

                       

                      This would essentially creates a new datasource

                      with the three identical (almost) copies of your original one.

                       

                      The difference is in the Tableau-generated [Table Name] field,

                      which could be used as a reference Dimension to calculate

                      both Measures Headers (the [YF : Measure Names] in my example),

                      and Measures Values (the number of [YF : Sales ...] calculated fields).

                       

                      2) My [YF : Sales ...] calculations (the three without the 'Top N' suffix)

                      are equivalent (almost) to the respective columns in your datasource --

                      with the important distinction regarding the [Table Names] usage.

                       

                      3) Additionally, the three Top N Sets would be created

                      from the [Customer] Dimension, using the respective

                      YF : Sales ... measure as a baseline Metric for each.

                       

                      4) Then the complimentary [YF : Sales ... Top N] Measures

                      would be created, each using the respective Set as an in-calc Filter.

                       

                      5) A final view could be built using a [Table Name] based Header Dimension

                      and a Dual Axis Bar-Gantt combo chart -- to have the same look as yours.

                       

                       

                      Please look at the Datasource Pane to understand the Pivot / Union re-shape approach.

                      Please refer to the columns named [YF : ...] to trace the calculation logic.

                      As for a final view, the Gantt Marks on the second Axis would do the trick.

                       

                      Hope it could help.

                       

                      Yours,

                      Yuri

                      1 of 1 people found this helpful