Sorting negative numbers with commas and decimal place

Sorting negative numbers with commas and decimal place

dgongdgong Posts: 3Questions: 1Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • rhinorhino Posts: 80Questions: 2Answers: 17
    edited July 2014 Answer ✓

    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?

  • dgongdgong Posts: 3Questions: 1Answers: 0
    edited July 2014

    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!

  • rhinorhino Posts: 80Questions: 2Answers: 17
    edited July 2014

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

  • dgongdgong Posts: 3Questions: 1Answers: 0

    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!

  • rhinorhino Posts: 80Questions: 2Answers: 17

    Glad you got it to work :)

This discussion has been closed.