Can't sort in the millions

kerryww

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

allan

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

kerryww

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

kerryww

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

allan

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

kerryww

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

allan

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

kerryww

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

mletson

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.

allan

Just add a `g` option to the regex replace to replace all occurrences.

Allan