cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
Introduction
 
When adding a break by field to an ordered column chart, the order does not exist anymore.
We have created this post in order to workaround this problem.
Steps

In order to use the script, you need to follow the next steps:
 
1. Edit the widget
2. In the widget Editor, click on the widget menu button and choose Edit Script (this will open a new page for the script editing)
3. In the widget Editor window paste the following script:
var categories= ["value desc"]; //Manual order\"value asc"\"Value desc"
var breakBy = [ ];
 
// ---------- 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){
 
 //If categories array contains "value asc" or "value desc", sort categories by their total values. 
 //The sorted categories will be placed in the category array used by the manual sorting script
 
 if (categories.length>0&&categories[0] === "value desc"){
 categories = sortColumnsByValue(ev,"desc");
 }
 else if (categories.length>0&&categories[0] === "value asc"){
 categories = sortColumnsByValue(ev,"asc");
 };
 
 //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;
 });
 };
});



//Sort categories by total value: functions

//Sort ascending \ descending
 function sortCatsByValueAsc(a,b){
 var aValue = a.value;
 var bValue = b.value;
 return ((aValue < bValue) ? -1 : ((aValue > bValue) ? 1 : 0));
 }

function sortCatsByValueDesc(a,b){
 var aValue = a.value;
 var bValue = b.value;
 return ((aValue < bValue) ? 1 : ((aValue > bValue) ? -1 : 0));
 }

//replace "category" object with the category name
function sortedCatsNames(arr){
 for (var i=0 ; i<arr.length ; i++){
 arr[i] = arr[i].name;
 }
 }

//Main function 
function sortColumnsByValue (arg, sorting){
 //Create an array to contain all categories' original index, name and value
 var cats = [];

 for (var c=0 ; c<arg.result.xAxis.categories.length ; c++){
 cats.push({index:c ,name: arg.result.xAxis.categories[c],value:0});
 }

 for (var i=0; i<arg.result.series.length; i++){
 for (var j=0 ; j<arg.result.series[i].data.length;j++){
 cats[j].value+=arg.result.series[i].data[j].y;
 
 }
 }
 //Sort Array by category value
 var sortCats;
 if (sorting==="desc"){
 sortCats = cats.sort(sortCatsByValueDesc);
 }
 else {
 sortCats = cats.sort(sortCatsByValueAsc);
 }
 
 //Replace objects with the category name
 sortedCatsNames(sortCats);
 return sortCats;
}
***All script rights reserved to Adi Lewenstein
Script Modifications
You can change the order type between Ascending (value asc) and Descending (value desc) in the first row of the script
 
var categories= ["value desc"]; //Manual order\"value asc"\"Value desc"
4. click on Save and close the window
5. Go back to the widget Editor page (Should still be open) and refresh the browser ( The script should already affect the widget )
6. Click apply
***Sisense orders the values in an alphabetical order, If you wish to order the values in a different order, create a new column and add a number at the beginning of each value according to the order you want them to appear. 
Version history
Last update:
‎02-13-2024 01:06 PM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: