Manually order a text column in a pivot

Follow

Arik

November 08, 2015 15:57

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:
   
   

3. 
   
   

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

• 

  Malinda Jepsen November 19, 2015 19:54

  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
• 

  Nancy Sipley July 22, 2016 17:56

  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
• 

  Dabin Hu September 07, 2017 08:45

  @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
• 

  Dabin Hu September 07, 2017 08:50

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

  0

  Comment actions Permalink
• 

  Kenneth Willingham May 09, 2018 13:14

  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
• 

  Jessica Orlando July 02, 2019 17:14

  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
• 

  An Y November 12, 2019 21:08

  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
• 

  An Y November 12, 2019 21:21

  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
• 

  Dhrubajyoti Das November 26, 2019 15:10

  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

Please sign in to leave a comment.