Manually order a text column in a pivot
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:
-
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 -
Add the field as a row in your Pivot, and sort by it:
-
-
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:
-
@Arik;
I can't see the picture in this pages.
Can you email me the solution?
Thanks.
-
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
-
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)
-
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 ONEAY
-
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
-
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!
Please sign in to leave a comment.
Comments
10 comments