Problem sorting formatted numbers

Problem sorting formatted numbers

rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

I have a column containing formatted amounts. If I sort them I get this result (German rendering):

-101,97 EUR
-131,54 EUR
-191,78 EUR
-31,81 EUR
-68,00 EUR

If I render them the English way I get this:

EUR -101.97
EUR -131.54
EUR -191.78
EUR -31.81
EUR -68.00

If I sort the other way around the result is equally wrong:

EUR -68.00
EUR -31.81
EUR -191.78
EUR -131.54
EUR -101.97

I guess data tables does not detect that these are formatted numbers and sorts them like strings. What could I do about it? I do all the number formatting on the server side with PHP.

In javascript I only do this:

columns: [
        {   data: "cashflow.interest_fee",
                render: function ( data, type, row ) {
                    return renderAmountCurrency(data, row.cashFlowCurrency);
                }    
            }
]

function renderAmountCurrency (amount, currency) {
    if (amount == '' || amount =='0') {
        return amount;
    } else {
        if (lang == 'de') {
            return amount + ' ' + currency;
        } else {
            return currency + ' ' + amount;
        }
    }
}

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited July 2017

    I dropped the currency to get closer to the problem:
    German rendering:

    -101,97
    -131,54
    -191,78
    -31,81
    -68,00
    

    English rendering:

    -31.81
    -68.00
    -101.97
    -131.54
    -191.78
    

    Looks like data tables can't handle the currency and it doesn't like a decimal comma either. I looked at the sorting plug ins and saw that this was all supposed to be working with data tables. The only plugin I use is the moment date time sorting plugin which works perfectly for me with only one line of code.

    I am using Data Tables 1.10.15

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited July 2017

    @allan, got the problem solved now by using this deprecated(!) plugin:
    https://datatables.net/plug-ins/sorting/formatted-numbers

    With the plugin I have no issues with any kind of format including the currency.

    "DataTables 1.10+ has formatted number detection and sorting abilities built- in. As such this plug-in is marked as deprecated, but might be useful when working with old versions of DataTables."

    Looks like there is a bug in current data tables version 1.10.15 because the plugin is still required. The issue is probably that the automatic detection of formatted numbers does not work. Using the plugin you need to tell it which columns should be sorted as formatted numbers. That is not really comfortable but better than wrong sorting. Please advise!

  • allanallan Posts: 61,440Questions: 1Answers: 10,053 Site admin

    I guess data tables does not detect that these are formatted numbers and sorts them like strings.

    Correct - because they contain non-numeric data. DataTables would automatically detect it if you used €101 for example, but not EUR.

    Instead what you need to do is use a plug-in such as this one.

    That plug-in won't correctly handle the comma for a decimal character though - you'd need to modify the plug-in slightly to let it cope with that.

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Well, that does not explain why data tables isn't recognizing the German rendered amounts even without any currency code or sign (see above). There is a decimal comma issue in the current data tables version regardless of whether or not a currency sign or an ISO currency code is included.

    In addition it would be great if you recognized the standard ISO-currency codes:
    https://en.wikipedia.org/wiki/ISO_4217

    In many cases it just isn't handy to use the symbols like € or $. The sterling sign isn't even on my keyboard ... And for many currencies there are no symbols to use.

    I am already using the plug-in you mentioned now. So I have a work around. That plug-in has no decimal comma issue. It works perfectly fine!

  • allanallan Posts: 61,440Questions: 1Answers: 10,053 Site admin

    There is a decimal comma issue in the current data tables

    Have you specified the language.decimal option? It appears to work okay in this example.

    Documentation for it is available here.

    In addition it would be great if you recognized the standard ISO-currency codes

    That's not something that is going to be added to DataTables core - but it sounds like a perfect candidate for a plug-in!

    Regards,
    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Yes, I have it in the default settings like this. But it does not seem to have any effect. Everything else works fine ...

        //Data tables language settings
        if (lang === 'de') {      
            $.extend( true, $.fn.dataTable.defaults, {
                "language": {
                    "decimal": ",",
                    "thousands": ".",
                    "info": "Anzeige _START_ bis _END_ von _TOTAL_ Einträgen",
                    "infoEmpty": "Keine Einträge",
                    "infoPostFix": "",
                    "infoFiltered": "(gefiltert aus insgesamt _MAX_ Einträgen)",
                    "loadingRecords": "Bitte warten Sie - Daten werden geladen ...",
                    "lengthMenu": "Anzeigen von _MENU_ Einträgen",
                    "paginate": {
                        "first": "Erste",
                        "last": "Letzte",
                        "next": "Nächste",
                        "previous": "Zurück"
                    },
                    "processing": "Verarbeitung läuft ...",
                    "search": "Suche:",
                    "searchPlaceholder": "Suchbegriff",
                    "zeroRecords": "Keine Daten! Bitte ändern Sie Ihren Suchbegriff.",
                    "emptyTable": "Keine Daten vorhanden",
                    "aria": {
                        "sortAscending":  ": aktivieren, um Spalte aufsteigend zu sortieren",
                        "sortDescending": ": aktivieren, um Spalte absteigend zu sortieren"
                    },
                    //only works for built-in buttons, not for custom buttons
                    "buttons": {
                        "create": "Neu",
                        "edit": "Ändern",
                        "remove": "Löschen",
                        "copy": "Kopieren",
                        "csv": "CSV-Datei",
                        "excel": "Excel-Tabelle",
                        "pdf": "PDF-Dokument",
                        "print": "Drucken",
                        "colvis": "Spalten Auswahl",
                        "collection": "Auswahl",
                        "upload": "Datei auswählen...."
                    },
                    "select": {
                        "rows": {
                            _: '%d Zeilen ausgewählt',
                            0: 'Zeile anklicken um auszuwählen',
                            1: 'Eine Zeile ausgewählt'
                        }
                    }
                }            
            } );        
        } else {
            $.extend( true, $.fn.dataTable.defaults, {
                "language": {
                    "select": {
                        "rows": {
                            _: '%d rows selected',
                            0: 'Click row to select',
                            1: '1 row selected'
                        }
                    }
                }
            } );
        }
    
  • allanallan Posts: 61,440Questions: 1Answers: 10,053 Site admin
    Answer ✓

    If you have a link to a test case showing the issue, I'd be happy to look into it.

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited July 2017

    Thanks for now, Allan. I appreciate your help very much! All of the amounts I am displaying are preceded (English) or followed (German) by an ISO currency code. For that reason the standard data tables functionality won't work for me anyway.

    I am happy to use the plug in you recommended. It has no issue with the ISO code and decimal commas. The only downside is that you have to declare which columns the plug in should consider as number formatted in columnDefs. No big deal either.

    After working with data tables and Editor for a while I would like to reiterate that I still like the product very much and what is really good is that there is always a way to overcome any roadblock one might run into. Particularly due to your awesome support!

  • allanallan Posts: 61,440Questions: 1Answers: 10,053 Site admin
    Answer ✓

    The only downside is that you have to declare which columns the plug in should consider as number formatted in columnDefs.

    A type detection plug-in could be used to resolve that. Perhaps something like:

    $.fn.dataTable.ext.type.detect.unshift( function ( data ) {
      if ( data.match(/\d/) ) {
        return 'formatted-num';
      }
      return null;
    } );
    

    The problem with that is that it is really aggressive. It would match date columns for example. Possibly you could add a check for an ISO code into it as well (.indexOf(...)) to make sure that it doesn't capture to much!

    I still like the product very much and what is really good is that there is always a way to overcome any roadblock one might run into. Particularly due to your awesome support!

    Thank you :)

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Hi Allan, the formatted-num plugin seems to have a bug. It thinks that -1.000.000,00 EUR is bigger than -800.000,00 EUR. The problem only occurs with a) German number formatting and b) if the number of digits is different.

    I decided to roll my own and also do the auto detection of formatted-num fields based on the ISO currency codes:

    //sorting of formatted numbers in English and German format
    $.extend( $.fn.dataTableExt.oSort, { 
        "formatted-num-pre": function ( a ) {
            if (lang == 'de') {
                a = a.replace(".", "");
                a = a.replace(",", ".");
            } else {
                a = a.replace(",", "");
            }
            a = a.replace( /[^\d]/g, "" );
            a = parseFloat(a);
            if ( ! isNaN(a) ) {
                return a;
            } else {
                return 0;
            }
        },
        "formatted-num-asc": function ( a, b ) {
                return a - b;
        },
        "formatted-num-desc": function ( a, b ) {
                return b - a;
        }
    } );
    //fields must contain an ISO currency code to be detected as formatted-numbers
    //since all table fields with ISO codes always contain a number this works.
    $.fn.dataTable.ext.type.detect.unshift( function ( data ) {
        var cur = [  'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
                     'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];      
        var i=0;
        while ( cur[i] ) {
            if ( data.search( cur[i] ) > -1 )   {
                return 'formatted-num';
            }
            i++;
        }
        return null;
    } );
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited August 2017

    @allan: I am not getting it done; I have no idea what is wrong. It still does not sort negative numbers correctly. -1,000, 000.00 is considered to be bigger than -100,000.00. I have attached a pdf. I sorted by column "Amount Remaining". Actually the result is as if I had nothing at all and just used the regular sorting without any extension.

    Here is my code (based on the formatted-num plugin):

    //sorting of formatted numbers in English and German format
    $.extend( $.fn.dataTableExt.oSort, { 
        "formatted-num-pre": function ( a ) {
            if (lang == 'de') {
                a = a.replace( /[\.]/g, "" );
                a = a.replace( /[\,]/g, "." );
            } else {
                a = a.replace( /[\,]/g, "" );
            }
            a = a.replace( /[^\d.-]/g, "" );
            a = parseFloat(a);
            if ( ! isNaN(a) ) {
                return a;
            } else {
                return '';
            }
        },
        "formatted-num-asc": function ( a, b ) {
                return a - b;
        },
        "formatted-num-desc": function ( a, b ) {
                return b - a;
        }
    } );
    
    var cur = [  'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
                 'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];    
    
    //fields must contain an ISO currency code to be detected as formatted-numbers
    //since all table fields with ISO codes always contain a number this works.
    $.fn.dataTable.ext.type.detect.unshift( function ( data ) {
        if (typeof data !== 'undefined') {
            if (data !== null) {
                var i=0;
                while ( cur[i] ) {
                    if ( data.search( cur[i] ) > -1 )   {
                        return 'formatted-num';
                    }
                    i++;
                }
            }
        }
        return null;
    } );
    
    

    Set breakpoints with my debugger. While "$.fn.dataTable.ext.type.detect.unshift" was triggered and worked properly "$.extend( $.fn.dataTableExt.oSort" never got started. I had set the breakpoint at line "if (lang == 'de') {"

  • allanallan Posts: 61,440Questions: 1Answers: 10,053 Site admin
    Answer ✓

    Could you run the debugger on the table for me please?

    Thanks,
    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Hi Allan,
    this is the debugger reference: ocutel

    The page has 5 tables. I sort the 4th Table (#tblCashFlow) by column 5 Amount Remaining cashflow.amount_remaining

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited August 2017

    @allan, now it gets really weird ... Even though "$.fn.dataTable.ext.type.detect.unshift" is being triggered and returns the proper value for columns containing an ISO currency code it still seems to cause the problem somehow.

    Because if I add this to columnDefs

    { type: 'formatted-num', targets: 5 }
    

    it suddenly works!

    So somehow the output of "$.fn.dataTable.ext.type.detect.unshift" is being ignored by data tables while the hard coded version above is being executed.

    debugger reference for this is apuqob

  • allanallan Posts: 61,440Questions: 1Answers: 10,053 Site admin

    Another possibility is that the type detection plug-in isn't matching the data you expected.

    This is the code:

    function(data) {
        if (typeof data !== 'undefined') {
            if (data !== null) {
                var i = 0;
                while (cur[i]) {
                    if (data.search(cur[i]) > -1) {
                        return 'formatted-num';
                    }
                    i++;
                }
            }
        }
        return null;
    }
    

    Which of the five tables in the debug trace is it that we are looking at specifically here?

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    I sort the 4th Table (#tblCashFlow) by column 5 Amount Remaining cashflow.amount_remaining

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Hi Allan,

    "Another possibility is that the type detection plug-in isn't matching the data you expected."

    Well, I stepped through this with the debugger and it is matching the data properly. I also use the exact same logic here and it works as expected:

    var cur = [  'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
                     'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];
    
    //custom button for cashflow csv generation
    $.fn.dataTable.ext.buttons.csvCashFlow = {
        extend: 'csv', fieldSeparator: csvSep, charset: 'UTF-8', bom: true,
                       filename: 'cashFlow',
        exportOptions: {
            format: {
                body: function ( data, row, column, node ) {
                    // Strip $ from salary column to make it numeric
                    if (typeof data !== 'undefined') {
                        if (data !== null) {  
                            if ( column > 11 ) {
                                return '';  //get rid of the changed manually column
                            }
                            var i=0;  //get rid of the currency ISO codes
                            while ( cur[i] ) {
                                if ( data.search( cur[i] ) > -1 )   {
                                    data = data.split(cur[i]).join("");
                                }
                                i++;
                            }
                            //rate must be adjusted to fraction to be able to calculate
                            if (column == 6) {
                                if (data !== '') {
                                    if (lang === 'de') {
                                        //replace the only comma with a period
                                        data = data.replace(",", ".");
                                    }
                                    data = data / 100;
                                    if (lang === 'de') {
                                        //replace the only period with a comma
                                        data = data.toString();
                                        data = data.replace(".", ",");
                                    }
                                }
                            }
                        }
                    }
                    return data;
                },
                header: function ( data, column ) {
                    if (typeof data !== 'undefined') {
                        if (data !== null) {
                            if ( column > 11 ) {
                                return '';  //get rid of the changed manually column
                            }
                        }
                    }
                    return data;
                }
            }
        }
    };
    

    Any further ideas please?

  • allanallan Posts: 61,440Questions: 1Answers: 10,053 Site admin

    Hi,

    Looking at the debug trace for #tblCashFlow, it shows that there is a row with the following data:

    1. Loan, Variable Rate
    2. Balance due on
    3. 20/09/2027
    4. 20/09/2027
    5. 20/09/2027
    6. Visible
    7. 41

    Its the first row in the data set for the table. Where is that coming from - I don't see it in the JSON response, but I also don't see RowGroup being used for that table.

    It looks to me like it is that data that wouldn't match the type detection since there is no currency in the columns where there is currency in other rows.

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited August 2017

    Yes, such a row exists it is actually not the first but the final row in the table. (Columns 3 and 4 (in your list 4. and 5.) are hidden in the standard display). The row indicates the final balance which is nothing because this it is a full repayment loan.
    I wasn't aware that it is not detected if the first row of a column does not contain the ISO code. Since Editor does not allow me to use ORDER BY I can't even change this or can I? I replaced the space in column 5 (in your list it is 6.) with "0 Euro" and it worked fine.

    So is this only an issue if the first row does not comply? Or is it an issue if any one row of a column does not comply? If the former is the case: is there a way for me to order the rows on the server properly so that the issue isn't there?

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    To be more precise: What I would really need is an auto-detect that detects the column if there is at least one row of the respective column that complies. Anything else is likely to cause problems.

  • allanallan Posts: 61,440Questions: 1Answers: 10,053 Site admin
    Answer ✓

    The first row isn't complying with the auto type detection as is. The simple solution would be to modify the type detection plug-in to allow for an empty string. Then modify the corresponding sort plug-in to treat an empty string as -Infinity or something.

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    It's not that easy I am afraid ... but can you tell me do all rows of a column need to comply or just the first one?

  • allanallan Posts: 61,440Questions: 1Answers: 10,053 Site admin
    Answer ✓

    but can you tell me do all rows of a column need to comply or just the first one?

    All cells in the column must comply or it won't be matched.

    Daft question perhaps, but why can't you add a check for an empty string?

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited August 2017

    Good morning Allan,
    sure I can add a check for an empty string ... Just wanted to make sure that this will not recognize other string columns that contain empty cells as formatted numbers. For that reason it is very good news for me that ALL cells in a column must comply! Maybe you can add that hint to the docs?!

    So here is my final solution:

    var cur = [  'EUR', 'USD', 'GBP', 'CHF', 'JPY', 'AUD', 'CAD', 'CNY', 
                 'DKK', 'SEK', 'NOK', 'CZK', 'PLN', 'HUF', 'RUB'         ];
    
    //auto detection only works if ALL cells of a column comply with the criterion !!!
    //fields must contain an ISO currency code or space to be detected as formatted-numbers
    //since all table fields with ISO codes always contain a number this works.
    //in addition German number fields should be detected as formatted-num regardless of
    //whether they contain an ISO code or not
    $.fn.dataTable.ext.type.detect.unshift( function ( data ) {
        if (typeof data !== 'undefined') {
            if (data !== null) {
                var i=0;
                while ( cur[i] ) {
                    if ( data.search( cur[i] ) > -1 )   {
                        return 'formatted-num';
                    }
                    i++;
                }
                if ( data === '') {
                    return 'formatted-num';
                }                
                if (lang == 'de') {
                    if ( ! moment(data, 'L', true).isValid() ) {
                        if ( isNaN(data) ) {
                            data = data.replace( /[\.]/g, "" );
                            data = data.replace( /[\,]/g, "." );
                            if ( ! isNaN(data) ) {
                                return 'formatted-num';
                            }
                        }
                    }
                }                    
            }
        }
        return null;
    } );
    
    //sorting of formatted numbers in English and German format
    $.extend( $.fn.dataTable.ext.type.order, { 
        "formatted-num-pre": function ( a ) {
            if (lang == 'de') {
                a = a.replace( /[\.]/g, "" );
                a = a.replace( /[\,]/g, "." );
            } else {
                a = a.replace( /[\,]/g, "" );
            }
            a = a.replace( /[^\d.-]/g, "" );
            a = parseFloat(a);
            if ( ! isNaN(a) ) {
                return a;
            } else {
    //14 digit negative number to make sure empty cells always stay at the bottom / top
                return -99999999999999;
            }
        },
        "formatted-num-asc": function ( a, b ) {
                return a - b;
        },
        "formatted-num-desc": function ( a, b ) {
                return b - a;
        }
    } );
    
  • allanallan Posts: 61,440Questions: 1Answers: 10,053 Site admin
    Answer ✓

    Good to hear you have it working.

    The fact that it checks all cells is implicit in:

    a companion type detection plug-in that will automatically scan a column's data and determine if the ordering plug-in can order the data in the column

    But I'll look at making that more explicit.

    Thanks for the feedback.

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    :+1: I need it very explicit, Allan. English is not my first language as you will have noticed for sure :smile:

This discussion has been closed.