1 2 Previous Next 21 Replies Latest reply on Jun 2, 2015 12:07 AM by Alexander Mou

# Top N Sets Not Working Correctly

Hello everyone -

After hours of trying to figure out what's wrong with my file I decided to put out there to see if someone can shed some light. There are lot of moving parts and calculations here and that is probably what is causing the Top N functionality fail.

As you can see on the pic attached there are a lot of other brands with higher volume which should be UNDER TOP 10 and for whatever reason their are not. I am choosing one specific time period and one specific retailer - so simplify things - but even then I can't seem to make it work

Ideas anyone?

Thank you so much,

Cristina

• ###### 1. Re: Top N Sets Not Working Correctly

Try looking through some of these posts that Jonathan Drummey compiled into a list on his site: Sorting and Top N | Drawing with Numbers

• ###### 2. Re: Top N Sets Not Working Correctly

Sets seems not good for your specific case.

Just try using a rank filter. Create a calc field [Rank] and drag it to filter shelf. Select True.

Rank(sum([Sales]))<=10

1 of 1 people found this helpful
• ###### 3. Re: Top N Sets Not Working Correctly

Hi Palo,

We can get this via index and top 10.

# Top & Bottom with other customers

By using Index( ) in calculated filed and drag that calculated field in rows shelf we can achieve this.Please find the attached twbx.I hope this solves your problem

Step1: Create calc field called index ie index( )

Step2: Drag the customer name and calc field index into row shelf.

Right click customer name descending and sort via field [sales]

Step3: create [Top N] parameter with integer data type

Step 4:Create calc field called top10? Title Top 10 calculations – Top10?

if [Index]<=[Top N] THEN "Top N"  ELSE IF [Index]>=SIZE()-[Top N]

THEN  "Bottom n"  else “Others” end end

Drag calculation field Top 10? Into filter then select top n and bottom n.

Drag the new calculation into row.

show parameter control and type 10,20 etc

Thanks

sankar

• ###### 4. Re: Top N Sets Not Working Correctly

Thank you ! The problem is I need to get ALL OTHER bucket after the INDEX() – ☹ Can’t seem to figure that one out!

• ###### 5. Re: Top N Sets Not Working Correctly

Create [Head and Tail] filter as:

if Rank(sum([Sales]))<=10 then 'Top 10"

else "Others"

end

You can always replace Top 10 by a parameter [Top N].

• ###### 6. Re: Top N Sets Not Working Correctly

Thanks Alexander – the calculated field goes to Measures not Dimensions – how can I make it list what the TOP 10 and OTHERS – so my bar chart would have 11 BARS on it?

Thanks a lot!

C

• ###### 7. Re: Top N Sets Not Working Correctly

Try this then, a technique by Joe Mako

http://community.tableau.com/docs/DOC-5274

I also wrote a piece based on % instead of rank. Technique is similar.

Vizible Difference: Grouping Lower % Bars in Bar Chart

• ###### 8. Re: Top N Sets Not Working Correctly

Does being measure or dimension matter in your case?

• ###### 9. Re: Top N Sets Not Working Correctly

Yes – because I ultimately need a chart by each BRAND+ SUM of ALL OTHER – so 11 bars. Thanks ☺

• ###### 10. Re: Top N Sets Not Working Correctly

Still struggling

I am attaching a packaged workbook after I tried to reproduce Joe Mako - Top N Parameters - but can't seem to make it work like on his file.

Any help is appreciated!

Thanks

• ###### 11. Re: Top N Sets Not Working Correctly

What are your desired results for the view?  Can you provide a visual mockup of what you hope to achieve here?  Cheers!

• ###### 12. Re: Top N Sets Not Working Correctly

Here it goes - thank you so much

• ###### 13. Re: Top N Sets Not Working Correctly

I'm confused because INDEX is the only measure in play in the sample TWBX I opened -- are you wanting to plot SUM(Sales) for the Top 10 and Others, or something else?

• ###### 14. Re: Top N Sets Not Working Correctly

Yes - that's exactly what I'm trying to do - I've tried lots of different ways and posted the last TWBX which I was trying to mirror Joe M file posted by Alexander above as an option to achieve what I was looking for. Because I have different Geographies, Time frames, etc - the option to use Tableau TOP N (set) did not work as it seems that it looks at all data not only what I am filtering by in each view.

I think I tried everything recommended above but nothing seems to calculate correctly top N (based on parameter) + Other.

The view I just posted is from a previous version where I almost got it to work however The top 10 brands being marked as top 10 were not correct - therefore I think it was ignoring the Geography and Timeframes chosen and just reporting on top 10 for all data.

See attached  - I can clearly see that Palmers and Curel are larger than Cetaphil - yet it is marking it as other and leaving Cetaphil in the Top 10.

Lost

1 2 Previous Next