9 Replies Latest reply on Aug 17, 2012 12:06 PM by William Weaver

# How do I convert a table to a Top 10 list (Part 2)?

I have created a table that displays the total number of trips taken between local cities during the first half of 2012. Now, I need help reducing the list of trips to just the Top 10 city to city combinations. When I posted this question originally, I had separated the data into two worksheets, one for the top destination cities and one for the top origination cities. The solution provided by Tracy Fitzgerald helped me to limit the list of cities in each worksheet to just the 10 cities.

Now my goal is to merge both lists of cities (origination and destination) into one worksheet and only display the combined Top 10 intra/intercity routes. After some trial and error and advice from Tracy, I created a set to merge both worksheets into a master list of city to city routes sorted from highest to lowest by a trip count. I have not, however, figured out how to convert this master list into a Top 10 list. Any helpful suggestions would be most appreciated.

I have attached a packaged workbook of my efforts if anyone wants to give this challenge a try.

Thanks - William

• ###### 1. Re: How do I convert a table to a Top 10 list (Part 2)?

Suggestion:  Start a new sheet. Create a new set (highlighting dimensions Origin City and Destination City and right-clicking to "Create Set..."  Create a Calculated Field called Rank with formula: Index().  Drag Rank to the filter shelf and limit the results to 1 through 10.

Note: This will only work if the set is sorted descending by the trip count.  I noticed that you used Count() to aggregate.  Might you instead want to use Count Distinct to get a count of distinct TripIDs?

• ###### 2. Re: How do I convert a table to a Top 10 list (Part 2)?

Thanks David. The suggestion to create and filter by a new calculated field using the formula, Index(), gave me exactly what I wanted. As for your other suggestion, what is the advantage in a switch to a Count Distinct? Do I risk counting the same trip twice with just a Count?

• ###### 3. Re: How do I convert a table to a Top 10 list (Part 2)?

Yep.  If your data looks like this, for example:

TripID     Leg     Minutes

1               a         90

1               b         60

2               a         15

If you do a Count(TripID), you'll get 3 because it simply counts the rows.  If you do a CountD(TripID) you'll get 2 because it counts the unique values in the TripID column.

• ###### 4. Re: How do I convert a table to a Top 10 list (Part 2)?

Thanks for the advice. The last thing I want to do is inflate my numbers.

• ###### 5. Re: How do I convert a table to a Top 10 list (Part 2)?

Hi David,

If it is not too late for a follow up question, I have created a new table with the same issue as before but where the Rank calculation field is not working. Would you have time to take a quick look?

Thanks --- William

• ###### 6. Re: How do I convert a table to a Top 10 list (Part 2)?

I'll give it a shot.  Can you attach or publish the workbook so I can dig in?

• ###### 7. Re: How do I convert a table to a Top 10 list (Part 2)?

Thanks David. I have attached the requested workbook.

• ###### 8. Re: How do I convert a table to a Top 10 list (Part 2)?

Found it!   By default, the Rank was being computed by "Table (across)" when you want it to compute by "Table (down)."  If you would have swapped rows and columns, it would have worked (you'd have seen the top 10 tripIDs across the columns).  But that's hardly what you want to see.

So, just drag the Rank field onto the Filters shelf, click the pill's dropdown, change the Compute Using value to Table (Down), and specify the range 1 to 10.

And remember that you probably want to use CountD instead of Count here, too.

Cheers.

1 of 1 people found this helpful
• ###### 9. Re: How do I convert a table to a Top 10 list (Part 2)?

Awesome. Thanks for the second round of help with my "Top 10" needs. Now that I understand how to use the "Rank" calculation field, I plan to make frequent use of it.

Thanks --- William