Negative currency

Robayb

I am having a hard time finding support for sorting negative currency.
I am using VB to format and if I use FORMATCURRENCY it gives me a value like -$100.00.

I am usuing the currency plugin and I set the Stype to currency...but it won't resolve

Please help

allan

Try this:

[code]
jQuery.fn.dataTableExt.oSort['currency-asc'] = function(a,b) {
/* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
if(b.substring( 1, 0 ) == '-'){
/* Remove the currency sign, after the minus! */
var x = a == "-" ? 0 : a.replace( new RegExp(/[^-0-9.]/g), "" );
var y = b == "-" ? 0 : b.replace( new RegExp(/[^-0-9.]/g), "" );
x = x.substring( 1, 0 ) + x.substring( 1 );
y = y.substring( 1, 0 ) + y.substring( 1 );
}
else{
/* Remove the currency sign */
var x = a == "-" ? 0 : a.replace( /,/g, "" );
var y = b == "-" ? 0 : b.replace( /,/g, "" );
x = x.substring( 1 );
y = y.substring( 1 );
}

/* Parse and return */
x = parseFloat( x );
y = parseFloat( y );

return x - y;
};

jQuery.fn.dataTableExt.oSort['currency-desc'] = function(a,b) {
/* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
if(a.substring( 1, 0 ) == '-'){
/* Remove the currency sign, after the minus! */
var x = a == "-" ? 0 : a.replace( new RegExp(/[^-0-9.]/g), "" );
var y = b == "-" ? 0 : b.replace( new RegExp(/[^-0-9.]/g), "" );
x = x.substring( 1, 0 ) + x.substring( 1 );
y = y.substring( 1, 0 ) + y.substring( 1 );
}
else{
var x = a == "-" ? 0 : a.replace( /,/g, "" );
var y = b == "-" ? 0 : b.replace( /,/g, "" );
x = x.substring( 1 );
y = y.substring( 1 );
}

/* Parse and return */
x = parseFloat( x );
y = parseFloat( y );
return y - x;
};
[/code]
I was kindly sent that modification to my code recently, but just haven't had a chance to put it up on the site yet...!

Allan

adambartholomew

Some of the stuff above looks a bit abnormal, I do not understand how it would work.
This works much better for me; although it is a bit heavier due to the regex.

[code]
jQuery.fn.dataTableExt.oSort['currency-asc'] = function(a,b) {
/* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
/* Remove the currency sign, after the minus! */
var x = a == "-" ? 0 : a.replace( new RegExp(/[^-0-9.]/g), "" );
var y = b == "-" ? 0 : b.replace( new RegExp(/[^-0-9.]/g), "" );

/* Parse and return */
x = parseFloat( x );
y = parseFloat( y );

return x - y;
};

jQuery.fn.dataTableExt.oSort['currency-desc'] = function(a,b) {
/* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
/* Remove the currency sign, after the minus! */
var x = a == "-" ? 0 : a.replace( new RegExp(/[^-0-9.]/g), "" );
var y = b == "-" ? 0 : b.replace( new RegExp(/[^-0-9.]/g), "" );

/* Parse and return */
x = parseFloat( x );
y = parseFloat( y );

return y - x;
};
[/code]

Zeus

I'm having the same issue. I pasted both of these functions into my page with no effect. As long as my table has all positive numbers(currency) it work great. I'm also using the format currency function in VBscript. Any pointers? My initialization script is below.



jQuery.fn.dataTableExt.oSort['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, "" );

/* Remove the currency sign */
x = x.substring( 1 );
y = y.substring( 1 );

/* Parse and return */
x = parseFloat( x );
y = parseFloat( y );
return x - y;
};

jQuery.fn.dataTableExt.oSort['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, "" );

/* Remove the currency sign */
x = x.substring( 1 );
y = y.substring( 1 );

/* Parse and return */
x = parseFloat( x );
y = parseFloat( y );
return y - x;
};

jQuery.fn.dataTableExt.oSort['formatted-num-asc'] = function(x,y){
x = x.replace(/[^\d\-\.\/]/g,'');
y = y.replace(/[^\d\-\.\/]/g,'');
if(x.indexOf('/')>=0)x = eval(x);
if(y.indexOf('/')>=0)y = eval(y);
return x/1 - y/1;
}
jQuery.fn.dataTableExt.oSort['formatted-num-desc'] = function(x,y){
x = x.replace(/[^\d\-\.\/]/g,'');
y = y.replace(/[^\d\-\.\/]/g,'');
if(x.indexOf('/')>=0)x = eval(x);
if(y.indexOf('/')>=0)y = eval(y);
return y/1 - x/1;
}

jQuery.fn.dataTableExt.aTypes.unshift(
function ( sData )
{
var sValidChars = "0123456789.-,";
var Char;

/* Check the numeric part */
for ( i=1 ; i

smh0427

I am new to jQuery and Datatables, so finding it difficult to sort negative currencies and percentages. My data has negative currency and percentages displayed in parenthesis like ($100) or (0.25%). How do I get the sorting on such data?

fbas

you can separate the display of the value from the actual value being used below the surface with the fnRender function and bUseRendered: false

else you'll need to parse your values and convert them to numbers in your sort routine(s)