Manually order a text column in a pivot

Follow
Avatar
Arik

Introduction

 

In some cases, you might need to sort a Pivot by a text column in a specific order.

For example, this small table:

 

Sisense allows text fields in alphabetical order, either ascending or descending.

To solve this, we’ll add another field in the ElatiCube to sort by, and then remove it from the widget.

 

steps:

 

  1. If you don’t have a field to sort by already, in the ElastiCube Manager, add a custom integer column, and define the order with a CASE-WHEN statement. For example:

    CASE

    WHEN item='one’ THEN 1
    WHEN item='two’ THEN 2
    WHEN item='three' THEN 3
    WHEN item='four’ THEN 4
    ELSE 9999
    END AS oredring





  2. Add the field as a row in your Pivot, and sort by it:



  4. Edit the Widget script and paste the following code:

    In the [fidx=0] parameter,  specify the column number, 0 being the first from the left.

     

widget.on("ready", function(widget, args) {

var CUR_COL_FIND_CRITERIA = "[fidx=0]"; 

//hide current column and set new width for the pivots
$('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).html("");
$('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).css("width", "0px");

});

///remove the sorting options from all the headers
widget.on('domready', function (a, b) {
$('.p-sort-head', element).css('display', 'none');
})



5. The Pivot will now be sorted by the hidden column and stay put. It could only be modified by the sorting menu above, in widget editing mode:



6

Comments

10 comments

Sort by Date Votes
  • Avatar
    Malinda Jepsen

    Can we do something similar for categories?  I need to order by one field and display another in categories and axis. 

    0
    Comment actions Permalink
  • Avatar
    Nancy Sipley

    This works well -- UNTIL you add the Contribution Quick Function.  Then everything says 100% :(  As many others have said, we really need to be able to determine our own sort order.

    1
    Comment actions Permalink
  • Avatar
    Dabin Hu

    @Arik；

    I can't see the picture in this pages.

    Can you email me the solution?

    Thanks.

    hudabin@commit-info.com

    0
    Comment actions Permalink
  • Avatar
    Dabin Hu

    Can the Bar Chart order by other categories and hide it?

    0
    Comment actions Permalink
  • Avatar
    Kenneth Willingham

    Was anyone able to get the pictures that are missing in the information above?  I have tried going through this with what was provided and haven't been able to make it work.  Or has there been any update in the tool itself with new version releases that will allow me to custom sort my columns in a pivot?

    Thanks

    2
    Comment actions Permalink
  • Avatar
    Jessica Orlando

    Does this script not work if the Pivot Table has columns? Since Columns are a main advantage and use for Pivot Tables, I would hope this could work.

    I copied the script and it removed my sort column from the visual, when I added it back, the field is still present but the values are just missing (instead of the full column disappearing)

     

    1
    Comment actions Permalink
  • Avatar
    An Y

    Hi Jessica 

    If you add the line commented as 'THIS ONE' shown as in  code  below, it will hide the first column 

    $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).html("");
    $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).css("width", "0px");
    $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).css("display", "none"); //<------THIS ONE

     

    AY

    0
    Comment actions Permalink
  • Avatar
    An Y

    Hi 

    I've  found that it works only if  the column you are hiding and sorting  on is the first one in 'Rows' , i.e.  fidx = 0  , and you can not sort on more than one column.

    Could someone else please validate , and offer a workaround - if there is any?

    AY

     
    0
    Comment actions Permalink
  • Avatar
    Dhrubajyoti Das

    I have the pivot configuration as below:

    Since, I want to keep "portfolio" at the last hence I have ranked it only as the highest. I have also added the below script but the rank column is not being hidden.

    widget.on("ready", function(widget, args) {

    var CUR_COL_FIND_CRITERIA = "[fidx=0]";

    //hide current column and set new width for the pivots
    $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).html("");
    $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).css("width", "0px");
    $('[id="' + widget.oid+ '"]').find(CUR_COL_FIND_CRITERIA).css("display", "none");
    });

     

    Please help

    0
    Comment actions Permalink
  • Avatar
    Delsaran Bigglesworth

    This worked for me in the new Pivot table 2.0

    widget.on('ready', function(){
        // Removes the content from the cell
        $('.table-grid__cell--col-0 .table-grid__content__inner', element).html("");
        // Shrinks the column to hide it
        $('.table-grid__column-0', element).css("width", "0px");
    });
     
    First I gave all the records an Id, then ordered them, then hide them with this script.
    Thank you for pointing me in a direction, Arik! 
    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post