Sorting numeric/currency data.

HarpsichordMan

I've run into problems sorting values formatted like this:
1,000.00
-$2,344.56
12.93 %
and have hacked some changes. I'm not a jscript guru, so optimize if you wish:
/*
* numerical sorting
*/
"numeric-asc": function(a, b) {
a = a.replace(",", "");
b = b.replace(",", "");
var x = a == "-" ? 0 : a;
var y = b == "-" ? 0 : b;
return x - y;
},

"numeric-desc": function(a, b) {
a = a.replace(",", "");
b = b.replace(",", "");
var x = a == "-" ? 0 : a;
var y = b == "-" ? 0 : b;
return y - x;
}
=========>
function(sData) {
/* Snaity check that we are dealing with a string or quick return for a number */
if (typeof sData == 'number') {
return 'numeric';
}
else if (typeof sData.charAt != 'function') {
return null;
}

var pos = sData.indexOf("$")
if (pos == 0 || pos == 1) {
return 'currency'
}
if (sData.indexOf("%") == sData.length - 1) {
return 'percent'
}


=================>

"currency-asc": function(a, b) {
/* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
var x = a == "-" ? 0 : a.replace(/,/g, "");
var y = b == "-" ? 0 : b.replace(/,/g, "");
//x = x.substring(1);
//y = y.substring(1);
x = x.replace('$', '');
y = y.replace('$', '');
x = x.replace(' %', '');
y = y.replace(' %', '');
/* Parse and return */
x = parseFloat(x);
y = parseFloat(y);
return x - y;
},
"currency-desc": function(a, b) {
/* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
var x = a == "-" ? 0 : a.replace(/,/g, "");
var y = b == "-" ? 0 : b.replace(/,/g, "");
//x = x.substring(1);
//y = y.substring(1);
x = x.replace('$', '');
y = y.replace('$', '');
x = x.replace(' %', '');
y = y.replace(' %', '');
x = parseFloat(x);
y = parseFloat(y);
return y - x;
},

"percent-asc": function(a, b) {
/* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
var x = a == "-" ? 0 : a.replace(/,/g, "");
var y = b == "-" ? 0 : b.replace(/,/g, "");
x = x.replace(' %', '');
y = y.replace(' %', '');
/* Parse and return */
x = parseFloat(x);
y = parseFloat(y);
return x - y;
},
"percent-desc": function(a, b) {
/* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
var x = a == "-" ? 0 : a.replace(/,/g, "");
var y = b == "-" ? 0 : b.replace(/,/g, "");
x = x.replace(' %', '');
y = y.replace(' %', '');
x = parseFloat(x);
y = parseFloat(y);
return y - x;
},

obviously, this is very useful for my US-only system but might help out a few folks.

allan

Hi HarpsichordMan,

Thanks for this. The reason it doesn't work off the bat is that DataTables is looking specifically for numeric data only to use with numeric sorting. So anything such as a % or comma will cause it to be treated as a string. It's for this reason that there is an API for extending to sorting with plug-ins, and there is a 'formatted numbers' plug-in already available: http://datatables.net/plug-ins/sorting#formatted_numbers . It will strip out anything non-numeric in a string and then do the sort numerically.

Either way will work nicely :-)

Regards,
Allan

HarpsichordMan

Thanks. I finally figured out how to use the API and found the add-on. Thanks for a great tool.