Sorting negative numbers with commas and decimal place

dgong

Hi everyone,

I'm having an issue sorting columns that have custom formatting. I saw another thread who had the exact same issue but it was closed without resolution: http://datatables.net/forums/discussion/13353/sorting-is-not-working-if-we-have-data-with-comma-negative-positive-values-and-two-decimal-values.

I've tried an approach similar to the poster above but it didn't work.
I also tried this:

    "mRender": function (data, type, full) {  
     if (type === 'display') {  
     return formatNumber(data, 2);  
     }  
     } else if (type == 'sort') {  
     return data;  
     } else if (type == 'filter') {  
     return formatNumber(data, 2) + " " + data;  
      }  
      return data;  
     }  

The data displays in the format I want, but when sorting messes up and filtering does too. If I remove mRender it sorts perfectly but I lose the desired formatting.

The following is an example of my column when it is sorted desc:

-125,639.71
-85,427.43
-55,116.49
7,875.60
69,002.44
109,405.46
110.04
184.06
658.37

Wondering if anyone else has come across this same issue and was able to come to a conclusion.
Any help would be greatly appreciated.

Thank you

rhino

You may need to change occurrences of return data; to return parseFloat(data);. Using that technique, I was able to create a working example here

Does that help?

dgong

First of all, thanks for the quick reply - I tried changing the return type but it doesn't sort as expected. I'm using an ajax data source rather than a dataset so I'm not sure if that makes a difference. Just out of curiosity, I tried a test dataset and sorting worked, but this won't do as I have to get data from the server.

I looked into custom type detection and sorting functions and am wondering if this could work? I changed the render function to the following:

"mRender": $.fn.dataTable.render.number(',', '.', 2, '')

As for custom sorting I tried a modification of this but it didn't work http://datatables.net/plug-ins/sorting/formatted-numbers. It never actually runs through that code.

What kind of works was something like this: https://www.datatables.net/examples/plug-ins/sorting_auto.html . My code is as follows:

     $.fn.dataTable.ext.type.detect.unshift(  
            function (sData) {  
                //include the dot in the sValidChars string (don't place it in the last position)  
                var sValidChars = "0123456789-.,";  
                var Char;  
                var bDecimal = false;  

                /* Check the numeric part */  
                for (i = 0 ; i < sData.length ; i++) {  
                    Char = sData.charAt(i);  
                    if (sValidChars.indexOf(Char) == -1) {  
                        return null;  
                    }  

                    /* Only allowed one decimal place... */  
                    if (Char == ".") {  
                        if (bDecimal) {  
                            return null;  
                        }  
                        bDecimal = true;  
                    }  
                }  

                return 'numeric-comma-decimal';  
            }  
        );  


     $.fn.dataTable.ext.type.order['numeric-comma-decimal-pre'] = function (d) {  
     var x = (d == "-") ? 0 : String(d).replace(/\,/g, "");  
     //alert("numeric-comma-decimal-pre " + d + jQuery.type(d) + " returned: " + x);  
     return parseFloat(x);  
      };  

However it still doesn't sort, but it now at least recognizes the custom type. I'm not sure if I have to add a 'numeric-comma-decimal-äsc' and 'numeric-comma-decimal-desc'. I've tried adding the following as well and got runtime errors.

     $.fn.dataTable.ext.type.oSort['numeric-comma-decimal-äsc'] = function (x, y) {  
            alert("numeric-comma-decimal-asc " + x + " " + y);  
            return ((x < y) ? -1 : ((x > y) ? 1 : 0));  
        };  

        $.fn.dataTable.ext.type.oSort['numeric-comma-decimal-desc'] = function (x, y) {  
            alert("numeric-comma-decimal-desc " + x + " " + y);  
            return ((x < y) ? 1 : ((x > y) ? -1 : 0));   
        };  

Any ideas?
Thanks again!

rhino

Unfortunately you've gone beyond the scope of my datatables knowledge. Perhaps someone more adept will come to help! ( @allan ?)

dgong

That won't be necessary as I got it to work :). The issue wasn't with the formatting but the fact that I was using http://tablesorter.com/ as my column header. I got rid of it and switched back to the data tables header using $.fn.dataTable.render.number(',', '.', 2, '') to format and ta da!

Thanks a lot your help - really appreciate it!

rhino

Glad you got it to work :)