Sorting Stacked Bar Graphs
Hi Everyone,
To my knowledge, there is no built-in way to sort stacked bar graph by the total height of the bar graph. A possible solution is to create a variable that is a sum of your other variables and then sort the graph based upon the sum variable. Although this will correctly sort the data, if you remove the total variable from the graph, the graph will revert to alphabetically sorting. I wrote a JavaScript that will sort a stacked bar graph based on the combined height.
Before:
After:
Code:
function sortLength(a,b){
return a[0] - b[0];
}
widget.on('render', function(se, ev){
//Creates a copy of the categories array.
var categories = se.queryResult.xAxis.categories;
var names = [];
for(var m = 0; m < categories.length; m++){
names.push(categories[m]);
}
var master = se.queryResult.series;
numVariables = master.length;
var variables = [];
for(var i = 0; i < numVariables; i++){
variables.push(se.queryResult.series[i].data);
}
//Creates an array with the value of all the variables summed together.
var total = [];
for (var i = 0 ; i < variables[0].length; i++) {
total.push(variables[0][i].y);
for(var j = 1; j < numVariables; j++){
total[i] += variables[j][i].y;
}
}
//sets one of the variables equal to this value, we will be able to sort on this value.
for(var i = 0; i < variables[0].length; i++){
variables[0][i].y = total[i];
}
varCopy = [];
for(var i = 0; i < numVariables; i++){
arrayToPush = [];
arrayToPush.push(variables[i][0].y); //Get a new array with the first element added to push into our copied array
varCopy.push(arrayToPush);
for(var j = 1; j < variables[0].length; j++){
varCopy[i].push(variables[i][j].y);
}
}
varCopy.push(names); //Add the names so they will also be sorted
var transpose = [];
for(var i = 0; i < varCopy[0].length; i++){
var storage = [];
for(var j = 0; j < varCopy.length; j++){
storage.push(varCopy[j][i]);
}
transpose.push(storage);
}
transpose.sort(sortLength); //Sorts the data based on the total variable.
//Remove the name in each of the arrays and adds it to an array of the sorted names.
sortNames = [];
for(t = 0; t < transpose.length; t++){
sortNames.push(transpose[t].pop());
}
//Re-transposes the array, which will be sorted.
var sortedVar = [];
for(var i = 0; i < transpose[0].length; i++){
var storageTwo = [];
for(var j = 0; j < transpose.length; j++){
storageTwo.push(transpose[j][i]);
}
sortedVar.push(storageTwo);
}
//Set the variable storing the sum of all variables back to its original value.
for(i = 0; i < varCopy[0].length; i++){
for(j = 1; j < numVariables; j++){
sortedVar[0][i] = sortedVar[0][i] - sortedVar[j][i];
}
}
//Changes the names of the xAxis to the sorted order
var categories = se.queryResult.xAxis.categories;
for(var i = 0; i < categories.length; i++){
categories[i] = sortNames[i];
}
//Changes the data to match the sorted values.
for(var i = 0; i < sortedVar.length; i++){
for(var j = 0; j < sortedVar[i].length; j++){
master[i].data[j].y = sortedVar[i][j];
}
}
})
Disclaimer: I'm a complete beginner at JavaScript and at Sisense, so any feedback or suggestions would be appreciated :)
Ian
Before.png
After.png
StackedSort.js
-
Hi Ian,
I've created a similar script that works without cloning data objects. This script also allows for filter selection/drilldown and for showing data labels for the total of each column. Hope this helps!
-Takashi
ColumnChart-SortStackedColumnsByTotal.zip -
Takashi do you know why with your script it changes my Date to to a full date format?
Also is there a way to change this so it keep the format by Date instead of by total so June, July, Aug etc. but still with the total at the top of each stacked chart?
Thanks
Before Script.PNG
After Script.PNG -
Hi Takash, Andrew,
I am also having this datetime problem ...how did you fix it?
Thanks!
DateTime_Issue.PNG -
Karoon/Tripti,
I just updated the script to exlude the run function:
widget.on('processresult',function(s,e){
/*
// User defined sort order
var sortOrder = "asc"; // Can be 'asc' or 'desc'
*/
// Show combined data labels
var showCombinedDataLabels = true;// Get the data
var data = e.result.series;// Get the number of category
var categories = e.result.xAxis.categories;
var numCategories = categories.length;// Save the totals for each data series
var totals = [];// Loop through each data series
for (var i=0; i<numCategories; i++){// Init a variable to hold to total for the column
var total = 0;// Loop through each column to get the total
for (var j=0; j<data.length; j++) {
total += data[j].data[i].y;
}// Loop through each column and add a data point for the total
for (var j=0; j<data.length; j++) {
data[j].data[i].total = total;
}// Save the total to the series
var totalObj = {
'total': total,
'label': data[0].data[i].selectionData[0],
'index': i
};
totals.push(totalObj);
}// Function to sort the totals ascending
function sortAsc(a,b) {
if (a.total < b.total)
return -1;
if (a.total > b.total)
return 1;
return 0;
}// Function to sort the totals descending
function sortDesc(a,b) {
if (a.total < b.total)
return 1;
if (a.total > b.total)
return -1;
return 0;
}/*
// Run the function to sort the columns based on total values
if (sortOrder == "asc") {
// Sort the data
for (var i=0; i<data.length; i++){
data[i].data.sort(sortAsc);
}
// Sort the labels
totals.sort(sortAsc);
} else {
// Sort the data
for (var i=0; i<data.length; i++){
data[i].data.sort(sortDesc);
}
// Sort the labels
totals.sort(sortDesc);
}
// Sort the xAxis labels based on the totals
for (var k=0; k<totals.length; k++){
e.result.xAxis.categories[k] = totals[k].label;
}
*/
// Should we show the total data labels?
if (showCombinedDataLabels) {
// Show the stack totals
e.result.yAxis[0].stackLabels = {
enabled: true,
color: 'black',
mask: e.result.series[0].mask,
formatWithCommas: function(x) {
return Math.round(x).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
},
formatter: function (){
var func1=this.options.mask;
var func2=this.options.formatWithCommas;
return defined(func1)?func1(this.total):func2(this.total)
}
};
}})
Let me know if this works for you
-
Nick,
Here's another simple way to sort stacked column/bar charts based on any measure you create.
Use this Javascript code in your widget:
widget.on('processresult',function(s,e){
var yValueIndex=2; // This is the measure (y-value series) that will be hidden by the script, and whose values you can use for sorting.
//Note that yValueIndex=2 means measure number 3 (the first measure has index value 0)
delete e.result.series[yValueIndex]; //deletes the measure that we want to hide.
});This code will hide the 3rd measure from the final widget. You can configure the script to hide any other measure by changing the value where it says: yValueIndex=2;
So if you wanted to hide the fourth value, use yValueIndex=3, etc.
Now, add the measure by which you want to sort as another value in your widget. Don't worry, it will not be displayed, just used for sorting.
Final step: click the sorting icon on the measure you added, and select ascending or descending.
Please sign in to leave a comment.
Comments
10 comments