-
1. Re: Top n vs the rest
Arjan Eriks Aug 17, 2011 9:03 AM (in response to Arjan Eriks)Maybe this workbook makes it more clear. I pumped the data in xlsx and included it in the workbook. Now it shows the top 3. I would like to see top 4 and 5 combined as 'other' if you catch my drift.
-
topnvsother.twbx 1.0 MB
-
-
2. Re: Top n vs the rest
Joe Mako Aug 17, 2011 12:09 PM (in response to Arjan Eriks)How about the attached?
Lots of good stuff going on in this workbook. I would like to detail the concepts at play when I get the chance.
(side note: this is not exactly the same approach used in http://www.tableausoftware.com/support/forum/topic/top-n-everyone-else-other , this attached workbook is more robust in my opinion)
-
topnvsother_edit.twbx 1.1 MB
-
-
3. Re: Top n vs the rest
Arjan Eriks Aug 17, 2011 1:27 PM (in response to Arjan Eriks)Joe, this is pretty amazing to be honest. It will probably take me some time to redo this stuff in my real workbook. Thanks so far! This is exactly what i want!
-
4. Re: Top n vs the rest
Joe Mako Aug 17, 2011 1:44 PM (in response to Arjan Eriks)Here is the breakdown of what I did:
- a Set of the two dimensions at play
- a duplicate of the top level dimension
- five calculated fields (could be condensed to four)
- the most interesting stuff happening in "Display Value" and would need to be edited to fit your situation
- "Database" is the only other formula that needs to be edited to apply
- two parameters to control the display and calculation
- the compute using for all table calc pills is the Set
- the Set is ordered on the measure field
- the "In Top n" pill is ordered so True is above False
and that is about it.
There are all sorts of other things that can be done, eg if you wanted to add in sub-totals, but modifications would need to be made to accommodate.
-
5. Re: Top n vs the rest
Joe Mako Aug 17, 2011 1:47 PM (in response to Arjan Eriks)here it is cleaned up, condensed to four calc fields. I noticed I had left an unnecessary calc in, and removed it.
-
topnvsother_edit2.twbx 1.0 MB
-
-
6. Re: Top n vs the rest
Arjan Eriks Aug 17, 2011 1:53 PM (in response to Arjan Eriks)Thanks again. Really appreciate the help. We are making our service reporting from xls based once a month to always real time, so I am in the middle of creating some very nice visuals based on OpenTSDB with Odata. When I am a bit further i'll show some concepts. But basically it is gathering data on every system (we have about 4000) every 5 minutes. Import this every night in an OpenTSDB database. And based on this database we will do trend analyses with Tableau. Keep you posted.
Oh and i probably have already one extra question, can i sum the averages of the 'other' category?
Cheers, Arjan
-
7. Re: Top n vs the rest
Joe Mako Aug 17, 2011 2:27 PM (in response to Arjan Eriks)> can i sum the averages of the 'other' category?
Yes, just change the formula for "Display Value" to:
IF [In Top n] OR [Show all Vendors] THEN AVG([Value]) ELSEIF INDEX()=[Select Top n]+1 THEN WINDOW_SUM(AVG([Value]),0,LAST()) END
-
8. Re: Top n vs the rest
David. Lewis Sep 15, 2011 11:44 AM (in response to Arjan Eriks)I kind of stumbled across this... Amazing. Very cool. I'm busy trying to understand it. thx Joe.
One question, though--why do you need to duplicate the top level dimension?
-
9. Re: Top n vs the rest
David. Lewis Sep 16, 2011 3:48 PM (in response to Arjan Eriks)Joe: I've been trying to replicate your method, but for some reason I cannot see my 'Show all Vendors' parameter selection has no effect on what is displayed -- all line items are display all the time. I took some sample data and rebuilt your worksheet step by step, several times now, with the same result. If you or someone else care to look at it and if you notice where I messed up, I'd appreciate it. thx (hopefully) D Lewis
-
topnvsothers_test.twbx 217.2 KB
-
-
10. Re: Top n vs the rest
David. Lewis Sep 16, 2011 4:38 PM (in response to Arjan Eriks)Found the problem! Thanks for looking at the thread, though! d
-
11. Re: Top n vs the rest
David. Lewis Sep 19, 2011 1:25 PM (in response to Arjan Eriks)Hi Joe: In case you are still reading this thread, looking closely at your workbook I see that the 'x Others' line item does not correctly show the sum of the lines. I don't understand the equations enough to troubleshoot it, though. Also, do you by any chance understand why your bars show the values as data labels as a default. I cannot get them to show up on my version unless I place the 'display value' pill on the label shelf. Just curious.
Also, in my version (as best I can tell it is a xerox copy of yours, with different data underneath) I do not get a bar generated for the 'x others' row. Anyone have any thoughts as to why? many thanks. d. lewis
-
topnvsothers_test.twbx 220.3 KB
-
-
12. Re: Top n vs the rest
Joe Mako Sep 19, 2011 1:38 PM (in response to Arjan Eriks)The formula for "Display Value" is averaging the quantity, not performing a sum. Would you like a sum or some other aggregation?
also you need to set the compute using for Display Value to the set "SubCode - Type".
I use the Label context menu to "Show all labels", I also turned stack marks off to get the label in the correct spot.
Changes are made in the attached.
-
topnvsothers_test_0_jm_edit.twbx 222.0 KB
-
-
13. Re: Top n vs the rest
David. Lewis Sep 19, 2011 2:35 PM (in response to Arjan Eriks)Thanks very much! I hadn't noticed it was an 'AVG' calc. !? This is very cool, and I'm off trying to find ways to add it everywhere. I just found the site with your video tutorials -- thanks for taking the time with them. Regards, d. lewis
-
14. Re: Top n vs the rest
James BakerOct 5, 2011 1:28 PM (in response to Arjan Eriks)
Excellently done.