8 Replies Latest reply on Aug 16, 2012 9:18 AM by William Weaver

How do I convert a table to a Top 10 list?

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 converting the table into a top 10 city to city combinations. At first, I thought the task would be easy but, despite repeated attempts, the best that I can do is list the Top 10 destinations for each origination city or vice versa.

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?

Hi William,

I've reattached your workbook with what I think is what you're after (I hope anyways!) and instructions.

Hope this helps!

-Tracy

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

Wow. The solution is a lot more complicated that I expected. I had assumed that I was overlooking some simple menu choice or check box to get the desired results. Thank you so much for the instructions. I do, however, have one more request. How do I restore the intercity counts (i.e. Asheville to Asheville) to the top 10 list? I admit my original description would seem to exclude the intercity trips but I do want to include them.

Thanks - William.

• 3. Re: How do I convert a table to a Top 10 list?

Hi William,

Simply remove the Origin=Destination calculation from the filter shelf.

-Tracy

• 4. Re: How do I convert a table to a Top 10 list?

Yes, after I removed the "origin=destination" filter and replaced the formula for the "Max Count of TripID" calculation field with "window_max(Count(TripID))", the Top 10 list of Inter/Intra-City Routes populated as desired. Thank you so much Tracy for your help with this issue.

Thanks - William

• 5. Re: How do I convert a table to a Top 10 list?

Hi Tracy,

After reviewing the Top 10 data, I had another thought. Would it be possible to create a Top 10 list of Inter-Intra-City Routes based on origination AND destination addresses? In other words, could I combine both Top 10 from the separate worksheets into a combined Top 10 list of routes?

Thanks - William

• 6. Re: How do I convert a table to a Top 10 list?

Hi William,

One idea might be to use a set. To create a set, highlight multiple dimensions, right click and select Create Set... This may cause some duplicates though. I'll continue to think of other possible solutions!

-Tracy

• 7. Re: How do I convert a table to a Top 10 list?

Hi Tracy,

Yes, creating a set made up of the Origin City and Destination City dimensions has put me tantalizingly close to my objective. The only step I cannot seem to figure out is how to reduce the list of routes to a Top 10 list. Could you take a look at the attached, packaged workbook and let me know what I need to do?

Thanks - William

• 8. Re: How do I convert a table to a Top 10 list?

Hi Tracy,

Please disregard the request for follow up help in my previous post above. I decided to open a new discussion thread called "How do I convert a Table to a Top 10 list (Part 2) which was subsequently solved by David Roers.

Thanks ---- William