Limit Chart Results

by joshholaday » Wed Aug 09, 2017 7:56 pm

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.
joshholaday
 
Posts: 9
Joined: Wed Aug 09, 2017 7:06 pm

by Nikita Kurguzov » Thu Aug 10, 2017 2:38 pm

Hello, Josh!
You can do it with this JavaScript:
Code: Select all
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.
User avatar
Nikita Kurguzov
 
Posts: 13
Joined: Fri Jul 14, 2017 1:35 pm

by joshholaday » Thu Aug 10, 2017 6:02 pm

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.
joshholaday
 
Posts: 9
Joined: Wed Aug 09, 2017 7:06 pm

by Nikita Kurguzov » Fri Aug 11, 2017 11:05 am

Try this code instead:
Code: Select all
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.png
Aggregation Field.png (4.14 KiB) Viewed 56 times


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.
User avatar
Nikita Kurguzov
 
Posts: 13
Joined: Fri Jul 14, 2017 1:35 pm

by joshholaday » Mon Aug 14, 2017 1:49 pm

This worked Nikita, thank you for your reply!
joshholaday
 
Posts: 9
Joined: Wed Aug 09, 2017 7:06 pm


Return to Dashboard Designer for SharePoint 2013