Limit Chart Results

#1

Good Afternoon Everyone,

I have a list that contains our companies purchase order details (i.e PO#, Date, Vendor, Amount, etc.). I would like to have a bar/column chart that will show the count of Purchase Orders created for our top 5 vendors. I would like this chart to dynamically update the vendors, not hard code them. For instance if a large PO is written for the 6th largest vendor and they surpass the 5th, I would like the chart to reflect so without the need to alter anything on the chart. Is this possible?

Thanks for any help/suggestions.

#2

Hello, Josh!
You can do it with this JavaScript:

handlers.requestSuccess = function(data, logger) {
  findLargest5();

  function findLargest5(){
    // sort descending
    data.items.sort(function(a,b) {
        if (a.Amount < b.Amount) { return 1; }
        else if (a.Amount == b.Amount) { return 0; }
        else { return -1; }
    });
		
    var topFive = new Array();
    for (var i = 0; i < 5; i++)
    {
    	topFive.push(data.items[i]); 
    }
    data.items = topFive;
	}
  return true;
}

Go to Dashboard Designer->Data Source->Advanced and replace handlers.requestSuccess with this code. You will also need to replace Amount, with the internal name of your field, by which you want to sort your vendors. If you use Aggregation for this chart, the code might need to be slighlty altered, so let us know.

Also, this would work even if the Vendors relative position on the chart would change. It would only break if you change the structure of your list, in that case you might need to do some alterations. But this code would update the chart automatically, just like you want it to.

#3

Thank you for the reply. I believe we’re on the right track, however I am using aggregation. I am totaling the $ value of each purchase order by the Vendor that the purchase was made from. When pasting your code with internal name alterations I am not seeing 5 vendors (only 4) and the totals/five that were chosen are not correct. As you stated above, this is probably due to an aggregation issue. If you would like me to export the chart and data, please let me know.

#4

Try this code instead:

handlers.aggregationSuccess = function(data, logger) {
  findLargest5();

  function findLargest5(){
    // sort descending
    data.groups.sort(function(a,b) {
        if (a.Total < b.Total) { return 1; }
        else if (a.Total == b.Total) { return 0; }
        else { return -1; }
    });
      
    var topFive = new Array();
    for (var i = 0; i < 5; i++)
    {
       topFive.push(data.groups[i]); 
    }
    data.groups = topFive;
   }
  return true;
}

Same as before, but this time replace handlers.aggregationSuccess.
Replace Total with the name of Aggregation field you have.

Aggregation Field.png995×267 2.89 KB

If this doesn’t work for you, we’ll need to take a closer look at the configuration of your chart to make sure it works in your case.

#5

This worked Nikita, thank you for your reply!