-
1. Re: Top N, with everyone else as "Other"
Richard Leeke Nov 18, 2010 2:49 AM (in response to Andy Cotgreave)Well that was a bit of fun, Andy - you're good at posting learning opportunities. Take a look at [sheet 2]. I summarised the others using WINDOW_AVG(AVG([Sales]) - not sure if that's what you want, but you get the idea.
Edit: Just figured out what the z was for in "z other" - I took that off and broke the sorting when you change the parameter. Have now added a hidden sort field.
-
Top n RL.twbx 984.7 KB
-
-
2. Re: Top N, with everyone else as "Other"
Jordan Cardonick Nov 18, 2010 5:59 AM (in response to Andy Cotgreave)What if you added an additional flag that followed the logic of
If Value is in the top N then return Customer_Name
else return "OTHER"
and use that as the column pill
I would imagine something like that would work.
edit: Nvm, that is exactly what Richard did.
-
3. Re: Top N, with everyone else as "Other"
Andy CotgreaveNov 18, 2010 8:06 AM (in response to Andy Cotgreave)
Richard - that's incredible. I still don't have my head round WINDOW_ functions. It's great to see WINDOW_AVG being used to pull info from multiple records into one cell.
Thanks very much.
The next question was going to be - can I now create a hierarchy so that I can expand/contract "Others". I think that's one step too far as the formulae create Measures, not Dimensions. Instead, I'll build an Action that will open another sheet to show Others' details.
Andy
-
4. Re: Top N, with everyone else as "Other"
Richard Leeke Nov 18, 2010 12:39 PM (in response to Andy Cotgreave)> can I now create a hierarchy so that I can expand/contract "Others"
If it's miracles you want you're going to need to consult Joe. ;-)
-
5. Re: Top N, with everyone else as "Other"
Joe Mako Nov 18, 2010 5:46 PM (in response to Andy Cotgreave)Here it is as a parameter (I modified some of the calcs).
As Christian Chabot said at the customer conference, "Your first question need no longer be, is it possible?"
"Assume it is and begin."
This would not be possible without Table calcs and parameters, I say it is the developers of Tableau who are the miracle workers.
-
Top n RL with condense option.twbx 989.9 KB
-
-
6. Re: Top N, with everyone else as "Other"
Andy CotgreaveNov 19, 2010 1:43 AM (in response to Andy Cotgreave)
Joe - you're a legend. Tableau developers - you're also legends. (you, too, Richard!) Hey, everyone's a legend!
Andy
-
7. Re: Top N, with everyone else as "Other"
guest contributor Nov 30, 2010 7:23 AM (in response to Andy Cotgreave)This is amazing. I will be using this technique a lot. Thanks guys!
-
8. Re: Top N, with everyone else as "Other"
Andy CotgreaveNov 30, 2010 8:06 AM (in response to Andy Cotgreave)
I've done a blog post on this solution:
http://www.thedatastudio.co.uk/blog/the-data-studio-blog/andy-cotgreave/dynamic-multi-member-groups
I haven't explained it, though. It's too complicated for me. However, I made it a bit prettier!
Andy
-
9. Re: Top N, with everyone else as "Other"
. Danihp Dec 2, 2010 1:20 PM (in response to Andy Cotgreave)Hi,
Sorry about this beginner question,
On Top n RL with condense option.twbx's sheet 2, Sort order and Custemer name are hidden. Both elements are in bold. What is the way to hide elements like this?
Thanks,
daniel.
-
10. Re: Top N, with everyone else as "Other"
Richard Leeke Dec 2, 2010 2:44 PM (in response to Andy Cotgreave)Right click on the field pill on the shelf and unselect "Show header" (which actually means "show column".
They are bold because they are sorted, but hidden because they are only needed for the sort order.
-
11. Re: Top N, with everyone else as "Other"
Craig Collier Dec 8, 2010 1:33 PM (in response to Andy Cotgreave)This is extremely useful. I actually thought that there was no feasible way to accomplish this. Well done gentlemen.
Have you given any thought to how this could be accomplished with many measures--say sales and profit perhaps? How about with a date dimension member such as QTR on the columns shelf?
Thanks,
Craig
-
12. Re: Top N, with everyone else as "Other"
Kyle B Jan 4, 2011 6:34 AM (in response to Andy Cotgreave)Good job guys - this should be a feature for "Top N" filtering. A simple checkbox (include "others"?).
-
13. Re: Top N, with everyone else as "Other"
Joe Mako Jan 11, 2011 8:27 PM (in response to Andy Cotgreave)Craig Collier,
If you provide some sample data and details on what you want the result of different selections to be, I expect an example solution could be made.
-
14. Re: Top N, with everyone else as "Other"
Brett Hull Feb 15, 2011 9:39 PM (in response to Andy Cotgreave)I am using the original example to select top vendors by spend and it is great
Can anyone tell me what I am doing wrong here I am using the top ( ) example and trying to get a total of the top ( ) in the chart using this table calc to use as a total of the top( ) but it gives the total of the top ( ) and others
Cheers
If [Sort Order]=1 and [Rank]<= [Top ( ) Master Vendors]
then
WINDOW_SUM( sum([F_Net_AUD]))
end