Can't sort in the millions

Can't sort in the millions

kerrywwkerryww Posts: 5Questions: 0Answers: 0
edited September 2012 in DataTables 1.9
I'm using DT vs. 1.9.3 and I can't get numbers to sort in the millions. It only wants to sort the first 2 digits. I've tried every plug-in available but keep getting the same results. I checked and rechecked for any junk code in the HTML but it appears clean.

The problem is on the fourth column. Here's the link to the page. http://tinyurl.com/bnsxv3p

TIA

Kerry

Replies

  • allanallan Posts: 63,389Questions: 1Answers: 10,449 Site admin
    You have non-numeric data in the column - specifically the string character `,` . To have this column sorted correctly, use the formatted numbers plug-in to strip the formatting and sort numerically: http://datatables.net/plug-ins/sorting#formatted_numbers

    Allan
  • kerrywwkerryww Posts: 5Questions: 0Answers: 0
    Thanks Allan,

    I added the new code and also took out all the [quote],[/quote] and replaced with [quote].[/quote] but still no love.

    http://tinyurl.com/9zqvfj3

    Kerry
  • kerrywwkerryww Posts: 5Questions: 0Answers: 0
    Hi Allan,

    In this test I took out all the non-numeric data - and it works!

    Here's the result http://tinyurl.com/cm58w55

    Unfortunately I'm going to have to find some way to include the commas or something to make the numbers readable. Presently users could confuse them with telephone numbers :)

    Any ideas greatly appreciated!

    TIA

    Kerry
  • allanallan Posts: 63,389Questions: 1Answers: 10,449 Site admin
    Hi Kerry,

    It is still string sorting that column. You need to either use a type detection plug-in for the formatted numbers, or set the sType - see these examples:

    With type detection: http://datatables.net/release-datatables/examples/plug-ins/sorting_plugin.html
    Without type detection: http://datatables.net/release-datatables/examples/plug-ins/sorting_sType.html

    Allan
  • kerrywwkerryww Posts: 5Questions: 0Answers: 0
    Hi Allan,

    It doesn't seem to matter which plug-in I use. I tried both of the examples above and it still refuses to sort passed the 2nd numeral. Of course all this changes if I take out the commas.

    Here it is with with the type detection plug-in: http://tinyurl.com/bnsxv3p

    I even tried taking out the "-" in [code]var sValidChars = "0123456789-,";[/code] But no luck.

    Do you think there's a bug in DT 1.9.3?

    Thanks again for your help.

    Kerry
  • allanallan Posts: 63,389Questions: 1Answers: 10,449 Site admin
    I think the problem is with the regex replace that is being used. Currently ou have something like:

    [quote]
    In: "10,000,000".replace( /,/, "." )
    Out: "10.000,000"
    [/quote]

    I'd suggest you want:

    [quote]
    In: "10,000,000".replace( /,/g, "" )
    Out: "10000000"
    [/quote]

    Allan
  • kerrywwkerryww Posts: 5Questions: 0Answers: 0
    Thanks Allan,

    Amazing as it is, it doesn't seem to matter what code I put in it still insists on only searching on the first 2 digits. Here's the latest attempt but still no go. I also tried the code at http://datatables.net/forums/discussion/5484/sorting-numbers-with-point-separator-for-thousand-1.99999-/p1 but no luck.

    [code]
    jQuery.fn.dataTableExt.oSort['numeric-comma-asc'] = function(a,b) {
    var x = (a == "-") ? 0 : a.replace( /,/, "." ).replace( /,/g, "" );
    var y = (b == "-") ? 0 : b.replace( /,/, "." ).replace( /,/g, "" );
    x = parseFloat( x );
    y = parseFloat( y );
    return ((x < y) ? -1 : ((x > y) ? 1 : 0));
    };

    jQuery.fn.dataTableExt.oSort['numeric-comma-desc'] = function(a,b) {
    var x = (a == "-") ? 0 : a.replace( /,/, "." ).replace( /,/g, "" );
    var y = (b == "-") ? 0 : b.replace( /,/, "." ).replace( /,/g, "" );
    x = parseFloat( x );
    y = parseFloat( y );
    return ((x < y) ? 1 : ((x > y) ? -1 : 0));
    };
    [/code]

    Kerry
  • mletsonmletson Posts: 1Questions: 0Answers: 0
    Has this been solved? I've spent a lot of time designing a report only to find that datatables cannot sort properly when a cell value has more than one comma.
  • allanallan Posts: 63,389Questions: 1Answers: 10,449 Site admin
    edited February 2013
    Just add a `g` option to the regex replace to replace all occurrences.

    Allan
This discussion has been closed.