Manually Order A Column Chart

[intapiuser]

Introduction

Sisense's built-in ordering allows you to sort columns by a displayed value, or in alphanumeric order. However, some situations require a more specific order. This article shows you how to utilize a specific order for the columns in a columns chart.

It's recommended that you only use this feature if you know exactly what categories will be returned in the chart. Unexpected values for your category will be left out.

Implementation

The following script was used in my environment to manually order the categories. You will need to change the values in the array 'categoryOrder'  and 'breakbyOrder'. If you do not wish to specify a sorting for the category or break by, leave the array empty (like [ ] ).   This script is also attached to the post. 

More information on how to edit a widget's script can be found here: https://sisense.dev/guides/customJs/extensions/

var categories= ['L','H','M','N\\A'];
var breakBy = [ '3', '4', '2', '1'];
 
// ---------- DO NOT EDIT BELOW THIS LINE ----------
 
var findIndex = function(val, list) {
 if (list=='breakby') { 
  return breakBy.indexOf(val) 
 } else if (list=='categories') { 
  return categories.indexOf(val)
 }
};

widget.on('processresult',function(se,ev){
 //determine if we should sort categories or breakbys
 categoryStatus=false;
 breakStatus=false;
 
 for (p=0; p<ev.widget.metadata.panels.length; p++){
  if (ev.widget.metadata.panels[p].name=="categories" && ev.widget.metadata.panels[p].items.length>0 && categories.length>0){
   categoryStatus=true;
  }
  else if (ev.widget.metadata.panels[p].name=="break by" && ev.widget.metadata.panels[p].items.length>0 && breakBy.length>0){
   breakStatus=true;
  }
 };
 
 if (categoryStatus){
  ev.result.series.forEach( function(bb) {
   newData= new Array(categories.length);
   var blankcat;
   bb.data.forEach( function(cat) {
    if (typeof cat.selectionData !== 'undefined'){
     newData[findIndex(cat.selectionData[0].trim(), 'categories')]=cat;
    }
    else{
     blankcat=cat; 
    }
   })
   for (i=0; i<newData.length; i++) {
    if (typeof newData[i] === 'undefined') {
     newData[i]=blankcat; 
    }
   }
   bb.data=newData;
  });
  ev.result.xAxis.categories=categories;
 };
 
 if (breakStatus) {
  ev.result.series.sort(function(a,b){
   if (findIndex(a.name, 'breakby') < findIndex(b.name,'breakby')) {
    return -1
   } else if (findIndex(a.name,'breakby') > findIndex(b.name,'breakby')) {
    return 1
   }
   return 0;
  });
 };
});