Is there a way to just display top 10 results in a bar graph?

  • 0
  • 2
  • Question
  • Updated 2 months ago
  • Acknowledged
Photo of Sania

Sania

  • 476 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 2
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Go to this ugly chart:

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=q&qid=84

Paste this code into the console:
var chart = Highcharts.charts[0];
var options = chart.options;
chart.destroy();
options.xAxis[0].categories = _.filter(options.xAxis[0].categories, function(item, index) {
  return index < 10;
});
options.xAxis[0].labels.step = 1;
options.series[0].data = _.filter(options.series[0].data, function(item, index) {
  return index < 10;
});
new Highcharts.Chart(options);
See screenshot of resulting Chart.



Then apply the Un-HighCharted Waters technique with the above code as described here:

https://community.quickbase.com/quickbase/topics/what-is-the-hylo-technique

If you needs additional assistance feel free to contact me off-world using the information in my profile:

https://getsatisfaction.com/people/dandiebolt
https://community.quickbase.com/quickbase/topics/hello-i-am-trying-to-create-a-bar-graph-report-but-...

I'm not sure where Eric landed.  I believe Cirrus ops is not longer.  Maybe you can find Eric on LinkedIn.  Unfortunately I did not make any cheat notes before I lost admin access.
Photo of QuickBase Girl

QuickBase Girl

  • 638 Points 500 badge 2x thumb
I'm trying to just run a DoQuery to get around this and then maybe slap some java magic on it for interface. Not having the best of luck doing this from scratch. 

Just need the top 10 clients after all the sales are combined. Has to be dynamic, and % would never work as they get new clients all the time.

At first I was just trying to manipulate a summary report, but that will never work for them. Pretty charts are needed. 

If anyone has a bit of code to scratch for this one, swing it my way. No 3rd party can be used. It has got to be home cookin'. 
Photo of QuickBase Girl

QuickBase Girl

  • 638 Points 500 badge 2x thumb
I'm just reaching out to Dan on this one. I feel he is the only one that has any solution for this one. This just seems like it is so frustratingly simple. :(
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 12,288 Points 10k badge 2x thumb
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. It is something that you might want to test first in a testing app to make sure it works out the way you would like and to get familiar with the set up. 
(Edited)
Thank you for posting this!
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
If script is not for strength, you can do this with native relationships and formulas.  It might not always show the top 10, but it will dynamically show your top % of results, and you can set that % to whatever you'd like.
Photo of Sania

Sania

  • 476 Points 250 badge 2x thumb
Can you elaborate on how would I take this route?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
>:If script is not for strength

You are killing me. Why would anyone choose not to use script when it is the universal way to solve QuicBase problems when native either doesn't work or becomes tedious.

>Can you elaborate on how would I take this route?

Follow my instructions above as a first step.

Actually there is a logic error in my script where I just modified the chart to take the first 10 values. I think what you may have wanted requires you to sort the array first and take the ten largest values. This is a simple modification using Underscore's sort() and first() methods .
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
Sania,

Can you provide an example of the report you are trying to make? 
I'm just looking for some of the defining factors that will dictate the needed set up.  
For example, is it a report of Projects grouped by category, client, or area, etc?

We will need to summarize all the data, then divide the data by its count so you can have a %, then set a % threshold, but I can provide more details if you have some more info for me.

.... or you can use a script...  I'm sure Dan would love to help.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
>I'm sure Dan would love to help.

Dan busy but I consider this problem solved already. I defy everyone to come up with a well formed problem that can't be solved with scrip! BAM
(Edited)
Photo of Michelle Chapman

Michelle Chapman

  • 690 Points 500 badge 2x thumb
Hi Matthew, I know this thread is 2 years old but I have the same question, except trying to draw out the top 5 on a bar chart. I was able to generate the bar chart that rates agent sales from highest to lowest, I just need to be able to display the top 5. Is there a formula I can enter in the chart formula box to display top 5? 
I believe you need to use Evan’s solution.
Photo of Jack Fretwell

Jack Fretwell

  • 804 Points 500 badge 2x thumb
I'm seeing an error with the above bar chart code;
Uncaught TypeError: Cannot set property 'options' of undefined.  
Has something changed in QB?
Sure this worked before.