Bar Chart Sort (Top 5)

The best post was by Evan, who is our forum moderator.
https://community.quickbase.com/communities/community-home/digestviewer/viewthread?MessageKey=225e551a-cc59-4386-88c8-d4d6a1c12614&CommunityKey=d860b0f8-6a48-487b-b346-44c47a19a804&tab=digestviewer

This is the useful part of that long thread.


Evan Martinez (Community Manager) commented on a reply to this question:
Is there a way to just display top 10 results in a bar graph?
I am actually familiar with the method that Eric used in his demo application for building out something like a top ten report and his method made use of native relationships and summary/lookup fields and a Master Parent record. Essentially it works best when you have at least two tables to start. In your instance if your client has a table of Customers and each customer has many records related to sales that are being summarized up to the Customer. You then create another table to serve as the home of your Master Parent record. From there you create a single record and then in your Customers table you create a numeric field and set it to be equal to 1 on all existing records and default to 1 for all new records and use this field as the reference field for your relationship so you have 1 Master record related to all your customers.

This way all of your customers are now related to your new parent record and you can start setting up your fields. If you want a top ten for example you create a look up field that is the Max of the field Total Customer Sales, you then set that new summary field as a look up down onto the Customer table. Then when you make your #2 customer summary field and do the Max of Total Customer Sales that are not equal to your #1 Customer look up field and then rinse and repeat till you have 10 summary fields calling up Customer 1-10 and 9 look up fields that you are using in the summary field to help you find the next Max so your last Summary field will be the Max of Total Customer Sales that are not equal to #1 Customer Summary through #9 Customer Summary. As the Customer Total Sales naturally fluctuates over time the relationship will change its order.

Then the final step is to create a formula field on your Customers table that is called Top Ten Customer ratings with a formula that says something like

If([Total Customer Sales]=[#1 Customer Lookup], 1,
If([Total Customer Sales]=[#2 Customer Lookup], 2,
etc.
Until you have your Customer 1-10 now rated by the formula and you set your Chart to show all customers who have a Rating of 1 or greater. It takes a decent amount of architecture that can long term have some possible considerations from a performance standpoint as a data set grows larger but it is possible and wouldn't require any outside scripting or third party services.


------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------