Melissa Fanucci wrote:
In other words, I'd like this table to break out the top 10-15 commodities crossing the border, and then group the rest in "other."
I'd like to confirm about the above requirement,
1) how do you define the top 10-15 commodities crossing the border? sum of all exports and imports?
2) what's the time range of the top 10-15? top 10-15 for from 2010 to 2015?
Hi Lei, thanks for responding. I'd like to separate out exports and imports because they will be different by direction.
Time range, I'd like to see what the top 10-15 exports/imports are by year to compare year over year changes in commodity. So ideally...
Top ten exports for 2010,2011,2012,2013,2014,2015
Top ten imports for 2010,2011,2012,2013,2014,2015
Thank you so much for any assistance you can provide!
To find the Top N (in your case 10) there are a few steps:
1. CTRL click on Year and Commodity Description and select Combine Field
2. Change Year to discrete and drag out Year and the newly combined field Commodity Description & Year onto the Rows shelf
3. Drag Sum(Exports by Truck, or whatever field you would like) onto the Columns shelf
4. Select the dropdown for the Commodity Description & Year field and select Sort
5. Select Sort By then your Field will be Exports by Truck (or whatever field you would like) and use Sum as the aggregation
6. Create a new calculated field called Index and the formula is simply: Index()
7. Convert Index to Discrete and drag it in between Year and Commodity Description & Year on the Rows shelf
8. Select the Index dropdown and go to Compute Using and select Pane(down)
9. Hold CTRL and drag the Index pill to Filters
10. Change the Index filter to Continuous and select 1-10
11. You are good to go! If you run into issues or it doesn't look right, make sure to check both the Index pill in Rows and Filters and that they are Computing Using: Pane(down)
From there you can right click on the Commodity Description and create groups for each year based on the Top 10 you have displayed for each year.
Hopefully this helps!
2015 Commodity Data (revised).twbx 189.3 KB
OMG GENIUS!!!!! Thank you so much! Of course while I was able to recreate it, I understood about 50% of what I just did. I guess I better do more reading on the differences between discrete and non-discrete values.
So is Index() specifically a calculation to do just this, e.g. rank items?
Thank you again. You just made my Tuesday much better.
1 of 1 people found this helpful
I'm glad I was able to help. Discrete (blue) and Continuous (green) pills are essential to using Tableau and understanding them will make your life 100 times easier. You'll
The INDEX function will be your best friend when it comes to more complex ranking/sorting. Basically, this table calculation will give you a rank field for every row or pane you can see in your viz. The RANK function has additional functionality allowing for addressing ties and other groupings based on rank.
Also, here is a very good site that contains almost everything you would want to know about Tableau from a former Zen Master: Data + Science
Best of luck!
Great resource, thanks!