sorting negative numbers

abickford

I have a dataset with positive and negative numbers in a column.
Data table sorts this column as if the values were strings, e.g.:
-102
-1085
-22
-230
-520
.
.
.
2
2560
38
495
56
56
934

Is there a way to sort this column numerically?
Setting the column to a numeric format, e.g.:

columnDefs: [
        { 'targets' : 2, 'type' : 'signed-num'}...

did not work,

colin

It's probably because there's a string in there somewhere, or some other format that's not a permitted numeric value - otherwise DataTables would treat it as a number.

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

By the way, there's another message from you caught in the spam folder - is that the same issue as this? Or should I unblock it?

Cheers,

Colin

abickford

Hi,
I was able to solve my sorting and formatting issues.
First, I removed the D3 formatting codes from the raw data. This solved the sort issue.
Then I applied the column,render formatting option to the columns.
All is well.

However, it took a while to figure out how to format a column as a percentage.
This syntax: DataTable.render.number( ',', '.', 1, '','%' ) is not the most legible. You might consider providing more examples in your docs.

Cheers-

abickford

I have posted about this issue a couple of times. Thanks for replying.
My table is available here: https://coloradodemography.github.io/WebsiteGrid/assets/html/profile.html
Select "Region" in the first dropdown, select "San Luis Valley" in the second dropdown, than click on the "Generate Profile" button.

The cells in this table are formatted using variations of the d3.format command, e.g:

    const fmt_pct = d3.format(".1%");
    const fmt_comma = d3.format(",");

The data tables code I am using is:

    $('#summtab').DataTable( {
        dom: 'Bfrtip',
       buttons: [
        {  
                extend: 'word',
                text :'Word',
                titleAttr: fileName,
                footer : true
            },
            {  
                extend: 'excelHtml5',
                title: fileName,
                footer : true
            },
            {
                extend: 'csvHtml5',
                title: fileName,
                footer : true
            },
            {
                extend: 'pdfHtml5',
                title: fileName,
                footer : true
            }
        ],
        "order": [],
        data: outtab,
        columns : labels,
        columnDefs: [
        { "type": "num-fmt", "targets": [1, 2,3,4,5,6,7,8] },
        {   
            'targets': 0, 'className': 'dt-body-left',
            'targets' : [1,2,3,4,5,6,7], 'className': 'dt-body-right',
        },
        { 'targets': 0, 'width': '20%' ,
          'targets' : [1,2,3,4,5,6,7,8], 'width' :'10%'
        }  
        ]
    } );

The problem is column 2 ("Population Change (2010 to 2019)") is not sorting as a numeric, while the other columns are sorting as numeric fields.
When I output the table in excel, the negative values are formatted as strings.
Could the problem be the assignments of the formats? Can you assign formats within the data tables command?

Any solutions would be appreciated.
TIA

colin

Has this been resolved? I just took a look and the sorting and the export appears to be working as expected:

Colin

p.s. and apologies about the multiple message, the spam filter caught them for some reason.