Sorting data on a bar chart by month/year


#1

Hi, I’ve read all the posts about sorting data on charts, but I still can’t get mine to work. I am grouping the data by month and year received, and I want to display each month/year on a bar chart. Here’s a snapshot of one of the views:

I want to display one bar per directorate, per month. This works, but the data is in the wrong order:
dd2

Please can someone explain to me how to get the data to sort properly so that the most recent month is on the left and the least recent on the right?


#2

Dear Alice,
Please, process and export the data for us, we’ll sort and provide both the code and the commentary, so you can configure it yourself in the future.You can either post it here or send it us by email to [email protected]


#3

Thank you very much Nikita, I have emailed it.


#4

Dear Alice,
Please, replace handlers.aggregationSuccess = function(data, logger) { … } with the following code in Data Source -> Advanced tab:

var categories = [];
handlers.aggregationSuccess = function(data, logger) {
  for(var i = 0; i < data.items.length; i++){
    categories.push(data.items[i].FOIMonthReceived); 
    data.items[i].Amount = 1;
  }
  
  var unique = categories.filter(function(itm, i, categories) {
    return i == categories.indexOf(itm);
  });
  
  categories = unique;
  
  for(var i = 0; i < categories.length; i++){
    var clone = $.extend({}, data.groups[0].items[0]);
    clone.Amount = 0;
    clone.FOIMonthReceived = categories[i];
    data.groups[0].items.push(clone);
  }
  
  data.groups[0].items.sort(function(a, b) { 
    if ( a.FOIMonthReceived < b.FOIMonthReceived )
      return 1;
    if ( a.FOIMonthReceived > b.FOIMonthReceived )
      return -1;

    return 0;
  });
  
  return true;
}

It’s essentially the same code used in this article, just slightly adopted to the situation.

Please, also select the newly added Amount as Value in Dashboard Chart, it’s just there for technical purposes(so the technical items are ignored), but the data will be counted same way:


#5

The original solution didn’t work in IE but Nikita kindly sent me the following code to put in Data source - Advanced, and it works perfectly:

var categories = [];
handlers.aggregationSuccess = function(data, logger) {
for(var i = 0; i < data.items.length; i++){
categories.push(data.items[i].FOIMonthReceived);
data.items[i].Amount = 1;
}

var unique = categories.filter(function(itm, i, categories) {
return i == categories.indexOf(itm);
});

categories = unique;

for(var i = 0; i < categories.length; i++){
var clone = $.extend({}, data.groups[0].items[0]);
clone.Amount = 0;
clone.FOIMonthReceived = categories[i];
data.groups[0].items.push(clone);
}

data.groups[0].items.sort(function(a, b) {
if ( a.FOIMonthReceived < b.FOIMonthReceived )
return 1;
if ( a.FOIMonthReceived > b.FOIMonthReceived )
return -1;
return 0;
});

return true;
}