Sorting Stacked Bar Graphs

Comments

10 comments

  • Avatar
    Takashi Binns

    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!

    Screenshot.PNG

     

    -Takashi




    ColumnChart-SortStackedColumnsByTotal.zip
    1
    Comment actions Permalink
  • Avatar
    Ian

    Hi Takashi,

    The new script is awesome! Thank you for developing this.


    Ian

    0
    Comment actions Permalink
  • Avatar
    Andrew Block

    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
    0
    Comment actions Permalink
  • Avatar
    Andrew Block

    Takashi nevermind I was able to update the script to work.....Great job on this we have wanted this feature for awhile

     

    Thanks

    0
    Comment actions Permalink
  • Avatar
    Karoon

    Hi Takash, Andrew,

    I am having this datetime problem too...how did you fix it?

    Thanks!

    0
    Comment actions Permalink
  • Avatar
    Tripti

    Hi Takash, Andrew,

    I am also having this datetime problem ...how did you fix it?

    Thanks!




    DateTime_Issue.PNG
    0
    Comment actions Permalink
  • Avatar
    Andrew Block

    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

    2
    Comment actions Permalink
  • Avatar
    Nick Burleigh

    This is great guys! Now, is there a way to sort each column from largest segment to smallest segment? I've been trying to accomplish this to no avail for a while now. Any help would be much appreciated! Thanks!

    2
    Comment actions Permalink
  • Avatar
    Adi Hecht

    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.

     

     

    1
    Comment actions Permalink
  • Avatar
    An Y

    Hi Adi

    Any tip on how to make the script work for a Bar chart with Break-by?

    It doesn't allow to have more than one Value set for the widget. 

     

    An

    0
    Comment actions Permalink

Please sign in to leave a comment.