Listbox value sorting (descending)

[Anbarasan P]

Dear All,

I have a DATE column in data table and I have added that as filter in text area. I want to sort that filter values in Descending order.

The issue is I am unable to achieve this, kindly guide me if there is any work-around for this issue.

[Sean Riley]

To do this you need to convert your column to a string data type and then you can define a custom sort order. The custom sort order is not possible on date columns.

 

To do this, define your own custom sort order for the column in the Custom Sort Order dialog:

 

Select Edit > Column Properties.

Click to select the column you wish to modify the sort order for in the Columns and hierarchies list.

Go to the Sort Order tab.

Click on the Custom sort order radio button and click Configure....

Select the values and then use the buttons to reorder the values (move up, move down, move first, move last), reverse the order, or reset the order to the default.Once the order is as desired, click the OK button twice to save the changes.

 

 

Note: The Custom Sort Order is only available for columns of data type string, so all other data types will always be sorted in the standard sort order for that data type and will not have the option to apply a custom order. In that case, the only option is to create a new column which converts the original column value to a string and then use that new string column in the visualization. To do this you can create a new calculated column with an expression using the String() function such as:

 

String([myOriginalColumn])

 

Then you can create a custom sort order on your new calculated column which you then use in visualizations.

[Anbarasan P]

Dear Sean Riley,

 

Thanks a lot for detail explanation. This helps some extent but in custom sort order new values sites without any order. We have to adjust custom sort every time we do data refresh.

 

Not a problem, I will try and achieve through string data type.

[Sean Riley]

Hope it helps. It's not perfect, but is really the only option.

[Basil Shaikh]

Hey,

You can do this with the help ofan Iron Python Scipt.

from System.Reflection import Assembly

from Spotfire.Dxp.Data.Collections import *

from System.Runtime.Serialization import ISerializable

from System.Collections import IComparer

from System.Collections.Generic import IComparer

 

values = Document.Data.Tables['MY_DATA_TABLE'].Columns['DATE'].RowValues.GetEnumerator()

myValues = []

for val in values:

myValues.Add(val.ValidValue)

myValues.sort(reverse=True)

Document.Data.Tables['MY_DATA_TABLE'].Columns['DATE'].Properties.SetCustomSortOrder(myValues)

 

Replace 'MY_DATA_TABLE' and 'DATE' with your respective table and date column name.

The above script upon execution will reverse the sort order.