9 Replies Latest reply on Aug 2, 2013 5:15 AM by Jim Wahl

    Top N % pie chart plot

    Suhrid Ghosh

      Hello ,


      I am trying to achieve the following :

       

      I have one table having the following columns : Dealer_Name , Date_Time , Recharge_Count , Recharge_Value.


      Need to plot a pie chart for TOP N % Dealer's against Recharge_Count . I have managed to do for TOP N Dealer's but for TOP N % Dealer's facing a tough time.


      Please give some guidance on this.



      Regards,

      Suhrid R. Ghosh

        • 1. Re: Top N % pie chart plot
          Brad Llewellyn

          Suhrid,

           

          There is no built-in functionality for Top N %, you will have to use table calculations to achieve.  I demonstrate the method in the following blog post:

           

          Breaking BI: Top N% Filters in Tableau

           

          Thanks,

           

          Brad Llewellyn

          Associate Data Analytics Consultant

          Mariner, LLC

          brad.llewellyn@mariner-usa.com

          http://www.linkedin.com/in/bradllewellyn

          http://breaking-bi.blogspot.com

          1 of 1 people found this helpful
          • 2. Re: Re: Top N % pie chart plot
            Suhrid Ghosh

            Hello Brad ,

             

                             Thanks for you reply. Your blog really helped me plotting my scatter plot for Top N % Dealer . However for pie chart i din got any much help but i have figured it out by myself . Here is the sample worksheet where i have done the Top N % Dealer pie chart plotting.

             

             

            Thanks & Regards,

            Suhrid R. Ghosh

            • 3. Re: Re: Top N % pie chart plot
              Jim Wahl

              Nice job. Thanks for posting the result.

               

              A couple of minor comments:

              • In the In top N%? formula, you can use SIZE() instead of WINDOW_COUNT(MIN([Dealer Name])). It's a bit cleaner and maybe faster.
              • The Top N% parameter may look better as a float formatted as a percentage with no decimal.

               

              Then your In Top N% formula is just: INDEX() <= SIZE() * [Top N%]. Which is easy to understand.

               

              Jim

              1 of 1 people found this helpful
              • 4. Re: Re: Re: Top N % pie chart plot
                Suhrid Ghosh

                Jim really a load of thanks for you valuable time and expertise suggestions .

                 

                 

                 

                Regards,

                Suhrid R. Ghosh

                • 5. Re: Re: Re: Top N % pie chart plot
                  Suhrid Ghosh

                  Hi Jim,

                   

                            Got stuck on one more thing that i am trying to achieve in the same attached workbook but facing a tough time. I want to show percentage of each slice in the tool tip. How can this be achieved. Can you please look on to this.

                   

                   

                  Thanks & Regards,

                  Suhrid R. Ghosh

                  • 6. Re: Top N % pie chart plot
                    Jim Wahl

                    Hi Suhrid,

                     

                    Isn't it incredible how something so seemingly easy is so difficult.

                     

                    I think this is possible, but will require a new approach. You can't use measure names / measure values to color / size the pie slices, since formulas can't reference the measure names / value fields.

                     

                    I'm out of time for today, but will give it a look tomorrow morning (Europe time).

                     

                    Jim

                    • 7. Re: Re: Top N % pie chart plot
                      Jim Wahl

                      OK, a little sleep helped.

                       

                      The key issue here is

                      1. Top N% requires a table calc---using either the SIZE() or WINDOW_COUNT() approaches.
                      2. Table calcs are always measures, even they look like dimensions; for example: Name = IF [In Top N%] THEN "Top N" ELSE "Other" END. You can't move Name to the Dimensions area in Tableau.
                      3. You can only partition table calcs on dimensions. Therefore you can't create a measure WINDOW_SUM(...) partitioned on Name.

                       

                      There are a couple of workarounds: First is to create individual measures for each partition and use Measure Names / Measure Values in the view. This was your original solution, which I thought worked nicely, but prevents you from doing secondary calculations on the values, since measure names / values can't be referenced in a calculated field.

                       

                      The second approach is to manually create the partitions. There several variations of this. I'm using the simplest, because your view requirements are simple---just two categories in a pie chart.

                       

                      Here's how I did it. All of the formulas I created are prefaced with the last letter of the alphabet (if I use that letter here too many times, Tableau's forum software may reject the post as spam).

                       

                      The key fields are Name and Value. Name is category name:

                      IF [In top N%]

                          THEN "Top N"

                          ELSE "Other"

                      END

                       

                      Value returns the WINDOW_SUM() for each partition:

                      IF FIRST() == 0 THEN

                          WINDOW_SUM(SUM([RechargeValue (SCR)]), 0, [Z-Top N position]-1)

                      ELSEIF FIRST() == -([Z-Top N position]) THEN

                          WINDOW_SUM(SUM([RechargeValue (SCR)]), 0, SIZE()-[Z-Top N position]-1)

                      END

                       

                      Top N position is just SIZE() * Top N%. In your example there are 381 dealers in the view and 38 dealers in the top 10%.

                       

                      The IF / ELSEIF clauses specify the partitions using the FIRST() function. FIRST() returns the position of the first row relative to the current row. We know that the Top N partition starts at the first row, so we can use a FIRST() ==0 to check for this. And the "Other" partition starts at position 38 (the 39th row, since the index starts at 0). In this case the first row is 38 rows back and FIRST() == -38.

                       

                      Next the WINDOW_SUM functions aggregate the values within each partition. The key is the start, end values in WINDOW_SUM(..., start, end). For the Top N partition we want the first 38 values; starting at the current row (start = 0) and ending 38 rows later (end = 37). For the Other partition, we again start at the current row (start = 0) and want the remaining 381-38 = 343 values, which is SIZE() - Top N position.

                       

                      Lastly I created a Value % field for your tooltip or label:

                           [Z-Value] / TOTAL(SUM([RechargeValue (SCR)]))

                       

                      Here's the new result is on the right below, next to your original measure names/values approach.

                      2013-08-02 11-42-13.png

                       

                      I left a "Work" worksheet that shows the values FIRST(), INDEX(), Top N, SIZE() ... values and may help you verify and understand the solution.

                       

                      Jim

                      • 8. Re: Re: Re: Top N % pie chart plot
                        Suhrid Ghosh

                        Jim , i couldn't have done this with out you. Excellent !!! . Thanks for posting the result and the kind of solution you gave , simply loved it !!!


                        once again thanks a lot  ...

                         

                         

                         

                        Regards,

                        Suhrid R. Ghosh

                        • 9. Re: Re: Re: Top N % pie chart plot
                          Jim Wahl

                          No problem . Have a great weekend.

                           

                          Jim