-
1. Re: Top N % pie chart plot
Brad Llewellyn Jul 30, 2013 5:10 AM (in response to Suhrid Ghosh)1 of 1 people found this helpfulSuhrid,
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
-
2. Re: Re: Top N % pie chart plot
Suhrid Ghosh Jul 31, 2013 5:32 AM (in response to Brad Llewellyn)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
-
Top N Dealer plot.twbx.zip 66.2 KB
-
-
3. Re: Re: Top N % pie chart plot
Jim Wahl Jul 31, 2013 5:45 AM (in response to Suhrid Ghosh)1 of 1 people found this helpfulNice 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
-
4. Re: Re: Re: Top N % pie chart plot
Suhrid Ghosh Jul 31, 2013 5:58 AM (in response to Jim Wahl)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 Aug 1, 2013 3:05 AM (in response to Jim Wahl)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 Aug 1, 2013 9:51 AM (in response to Suhrid Ghosh)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 Aug 2, 2013 1:59 AM (in response to Jim Wahl)OK, a little sleep helped.
The key issue here is
- Top N% requires a table calc---using either the SIZE() or WINDOW_COUNT() approaches.
- 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.
- 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.
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
-
Top N Dealer plot_jimw.twbx.zip 131.5 KB
-
8. Re: Re: Re: Top N % pie chart plot
Suhrid Ghosh Aug 2, 2013 2:45 AM (in response to Jim Wahl)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 Aug 2, 2013 5:15 AM (in response to Suhrid Ghosh)No problem . Have a great weekend.
Jim