I would like to display Top 5 values as bars and Bottom 5 values as bars , i mean two bar graphs in same worksheet. In the above link, in a single graph it is showing percentages.
I created a sample worksheet to show Top 10 and Bottom 10 in same worksheet:
The trick here seem to be creating Sheet1 with Top10 and Sheet2 with bottom10 and then creating a dashboard to use them both in one sheet. There may be other solutions as well but i was able to come up with this one.
Top 10 and Bottom 10 arrived ofcourse by using Filter Dialogue box on Customer Name, Attached is a screengrab for the same using psr utility of windows.
Filter Dialogue Box.zip 719.9 KB
Thank you Anoop for your information. Can we create the two grpahs in same work sheet instead of creating two bar graphs for Top and Bottom values in different sheets and combining in dashboard.
Only way i can think of is to use custom calculations and using a filter which shows Top 10 and Bottom 10 in same sheet based on user input. Limitation is both can't be shown at same time using this method. Hope somebody in community can answer this.
You might find more information by searching the forums for TopN.
The below strategy should work for you:
1. Create two parameters -- even if you don't present these to the end user, I find them helpful for debugging / testing. ...
1.1 Show all values > Data type boolean, default to False
1.2 Show top and bottom N (Int), default to say 5---if you set min and max values, you can use a slider in the parameter control.
2. Create three calculated fields -- these will form the core of your view
2.1. Display names
IF [Show all values] THEN ATTR([Product])
ELSEIF FIRST() == 0 THEN "Top " + STR([Show top and bottom N]) + " products"
ELSEIF LAST() == 0 THEN "Bottom " + STR([Show top and bottom N]) + " products"
2.2 Display values
IF [Show all values]
ELSEIF FIRST() == 0
THEN WINDOW_SUM(SUM([Sales]),0,[Show top and bottom N]-1)
ELSEIF LAST() == 0
THEN WINDOW_SUM(SUM([Sales]),LAST()-[Show top and bottom N]+1, LAST())
2.3. Display rank (used to sort)
IF [Show all values] THEN INDEX()
ELSEIF CONTAINS([Display names], "Top") THEN 1 ELSE 2 END
3. To create the view,
3.1 Add the dimension you which to show the Top / Bottom N for to the level of detail shelf. I used Product in the Coffee Chain example.
3.1.1 Right-click on dimension, click Sort > Sort Order descending and Field Sales Sum
3.2. Add Display names and Display rank to rows shelf. Make sure Display rank is "Discrete"
3.3. Add Display value to columns shelf.
3.4. Right-click on all of the Display table calcs and select Compute Using > Product -- If you've done sorted Product by Sum Sales (step 3.1.1), this should be all you need to do. Sorting is critical, since we're using the index() and window functions to sum top / bottom N.
3.5. Right-click Display rank and uncheck show header
TopAndBottom.twbx.zip 88.6 KB
I just reread your post and maybe you want to display the top / bottom N individually? If so, you don't need the Display value, Display name, and Display rank fields.
All you need is a calculated field to determine if the product is "In top or bottom n"
IF [Show all values] THEN "All"
ELSEIF INDEX() <= [Show top and bottom N] THEN "Top N"
ELSEIF SIZE() - INDEX() < [Show top and bottom N] THEN "Bottom N"
In the Products worksheet above, right-click on this pill, select Show Header. Right-click on middle > Hide. Now that worksheet should look like this.
TopAndBottom.twbx.zip 76.2 KB
Hooray for 8!
You can do this really, really easily without writing a line of code using Combined Sets - new in Tableau 8
- Create a Top X Set
- Create a Bottom X Set
- Combine the two sets into a combined set and drop that in your viz
- If you want to color, drop either the Top or Bottom set on your Color....
Top and Bottom.twbx.zip 265.9 KB
Cool! Very handy.
Thanks for the tip Russell.
This brilliant, Russell, thanks!
I tired your solution in my tableau workbook,it seems not to be giving me the exact picture.
All i need the top n and bottom n campaigns per region.
Steps i followed.
Created top and bottom parameter
created top x set
created bottom x set
combined both top x and bottom x set
dragged region and campaigns in row shelf
dragged combined top and bottom set in the filter
Showed up the parameter control
pulled top x set in color shelf.
Please tell me where am i going wrong.attached is the file
top n and bottom n.twbx.zip 34.9 KB
Hi Amby & Monika,
Hoping that your issue is same as the one described in the following link, suggest you going through the link which resolves your query of displaying top & bottom values in the same worksheet.