thousands sperator dosn`t work

fahrstuhl65

Hi,

i worked since two days with datatables and read a lot of regarding the configuration:

$(document).ready(function () {

    $('#dtDepotsAktuell').DataTable({

        // die deutschen Spracheinstellungen und TausenderPunkte usw.
        "language": {
            "decimal": ",",
            "thousands": ".",
            },
            "oAria": {
                "sSortAscending": ": aktivieren, um Spalte aufsteigend zu sortieren",
                "sSortDescending": ": aktivieren, um Spalte absteigend zu sortieren"
            }
        },

Here I set as described the thousand seperator, but it dosn`t work as i expectedt ?
can anybode help me ? with best regards

fahri

colin

It's working as expected here. We're happy to take a look, but as per the forum rules, please link to a test case, or modify the one above - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

fahrstuhl65

Hi Collin,

thanx a lot for your response !

it think i missunderstand the function ? As i understood i thought he convert the values as described in language part, but nothing happens if i change it vice versa - see this example:

http://live.datatables.net/yamubewa/1/edit

He should use comma for thousands and decimals should be the dot.

But nothing happens or where is my fault oder misunderstanding ?

best regards
Fahrig

rf1234

I think there is a misunderstanding with this. "decimal" and "thousands" in "language" don't apply to the data table content but to the data table controls. The example Colin quoted has to small numbers in the controls to show a difference if you change the parameters..
Just take a look at this post:
https://datatables.net/forums/discussion/59328

When it comes to formatting the content of a data table you would rather need something like this:

var numberRenderer;
if (lang == 'de') {
    numberRenderer = $.fn.dataTable.render.number( '.', ',', 2 ).display;
} else {
    numberRenderer = $.fn.dataTable.render.number( ',', '.', 2 ).display;
}

var myFormattedNumber = numberRenderer(someUnformattedNumber);

fahrstuhl65

This is the better example to show that it`s not worked as expected:

http://live.datatables.net/payoteza/1/edit?html,css,js,console,output

or i didn`t understand how it works ?

fahrstuhl65

@rf1234

Thanks a lot, so i understood it wrong, sorry for that !

Otherwise It is possible to use the Logic from your:

$.fn.dataTable.render.number( '.', ',', 2 ).display;

in this way that i never convert numbers in my scripts for some calculations ?
Because the numbers in dataTable never changed, only the displaying value in dataTable is formatted in this way ?

Otherwise In every case when i changed the original value i have to replace commas and dots befor calculations and afterwards ?

Sorry for my bad englisch - i hope you can understand what i asked for ? :-)

Best regards
Fahri

fahrstuhl65

Ok, in columnDefs i got it now to work:

{ "targets": [3, 6, 7, 8, 9, 10], "render": $.fn.dataTable.render.number('.', ',', 2, '') },

The last thing i have to solve is to implement the same in footerCallback where i calculate sums for some columns.

Any suggestions how to achieve this ?
I need the possibility to render in footercallback too ?

This is my actually footerCallback:

"footerCallback": function (row, data, start, end, display) {
                    
            var api = this.api(), data;
         
            spesentotal = api
                .column(7, { page: 'current' })
                .data()                
                .reduce(function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0);
            
                          
            $(api.column(7).footer()).html(spesentotal);
            
        },

kthorngren

You can chain display() onto the number renderer, like this:
$(api.column(7).footer()).html($.fn.dataTable.render.number('.', ',', 2, '').display( spesentotal ));

Here is a running example:
http://live.datatables.net/fefawuni/1/edit

Kevin

rf1234

@fahrstuhl65 von mir aus kannst Du auch auf deutsch schreiben. Verstehen aber nicht so viele. Dein Englisch verstehe ich allerdings auch nicht
Das hier ist sehr gut https://www.deepl.com/translator Hat auch ne schöne Windows app.
I hope it's all resolved for you now with Kevin's last post. If not just let us know.
Roland

rf1234

Just saw that. Yes you would need to convert numbers back and forth if you do calculations on the client. That is why I try to avoid this and do it on the server before rendering.

Here is an example on how to convert a formatted number back to one that you can calculate with.

if (lang == 'de') {
    rowTotal = rowTotal.toString().replace( /[\.]/g, "" );
    rowTotal = rowTotal.toString().replace( /[\,]/g, "." );            
} else {
    rowTotal = rowTotal.toString().replace( /[\,]/g, "" );
}

fahrstuhl65

@kthorngren

Thx a lot for your help - just implemented and works well !

@rf1234

Mit dem Englischen hast du sicher Recht :-) Ist über die Jahre doch stark eingerostet, wenn man solche Sachen im Alltag nicht mehr nutzt verlernt man das auch leider ! In diesem Sinne auch dir nochmal vielen Dank für die Hilfe !

fahrstuhl65

Hello Roland, there is another problem, I did not see that at first. It now displays the numbers correctly with the following configuration:

columnDefs": [
            
            { "targets": [5], "render": $.fn.dataTable.render.number('.', ',', 0, '') },
            { "targets": [6, 7, 8, 9, 10, 11, 13], "render": $.fn.dataTable.render.number('.', ',', 2, '') }
            
            {"targets": [11, 12, 13, 14, 17, 19, 23],
                "createdCell": function (td, cellData, rowData, row, col) {

                    // Remove the formatting to get integer data for summation
                    cellData = cellData.replace(',', '.');

                    if (cellData < 0) {
                        $(td).css('color', 'red')
                    }
                    if (cellData >= 0) {
                        $(td).css('color', 'green')
                    }
                },
            }
        ],

But actually it is supposed to round to two decimal places, which it does, or rather represents it that way, but it "swallows" the decimal places. If I get 281,81 from the DB, he represents it as 281,00. And always as 00 after the comma.

When summing in the footer he does it correctly and this is the same spelling, only that it is done there as follows:

var numFormat = $.fn.dataTable.render.number('.', ',', 2, '').display;

            if (currency === 'EUR') {
                $(api.column(7).footer()).html(numFormat(expensesTotal) + ' \u20AC');

Do you have an explanation or a way to solve that ?

with best regards
Fahri

colin

Can you update the test cases to demonstrate this, please.

Colin

rf1234

I am doing all the number rendering on the server with the one exception I posted because I need to do a client side calculation. So I am not quite the expert on this.

But one thing I can see here:

cellData = cellData.replace(',', '.');

This line is not going to work.It will create invalid numbers will multiple decimal points.
1.200.000,00 will be 1.200.000.00 but it must be 1200000.00.
If you take a look at my previous post you see what transformations you would have to to make for German formatted numbers and English formatted numbers.

In addition in this code for German formatted numbers really everything is important if I recall it correctly. Leaving out the toString() conversion will create a problem in certain instances (forgot in which). So why reinvent the wheel if the solution is already there? :-) So first get rid of the thousand separator points and then convert the comma to a point.

rowTotal = rowTotal.toString().replace( /[\.]/g, "" );
rowTotal = rowTotal.toString().replace( /[\,]/g, "." );           

fahrstuhl65

@ colin

it is not possible to create a test case - the data comes from MSSQL and in every test case the data are fix and not changed when i change something in configuration ?

But i just figured it out - this work as expected:

$(document).ready(function () {

    
    var numFormat = $.fn.dataTable.render.number('.', ',', 2, '').display;

        "columnDefs": [
            
            { "targets": [5], "render": $.fn.dataTable.render.number('.', ',', 0, '') },            
            { "targets": [6, 7, 8, 9, 10, 11, 13], "render": numFormat()},
            
            {"targets": [11, 12, 13, 14, 17, 19, 23],
                "createdCell": function (td, cellData, rowData, row, col) {

                    // Remove the formatting to get integer data for summation
                    cellData = cellData.replace(',', '.');

                    if (cellData < 0) {
                        $(td).css('color', 'red')
                    }
                    if (cellData >= 0) {
                        $(td).css('color', 'green')
                    }
                },
            }
        ],

So i have to use the way with numFormat() - but i don`t know why the other way only display every value with .00 ??

fahrstuhl65

@ collin & rf1234

I was happy too early. This change only works because it "ignores" it - it is not a function either. It then correctly represents the decimal places, but the thousands of separators are gone again.

I can't build a test case like this, does anyone else have an idea how to solve this?

<br>$(document).ready(function () {
 
     
    var numFormat = $.fn.dataTable.render.number('.', ',', 2, '').display;
 
        "columnDefs": [
             
            { "targets": [5], "render": $.fn.dataTable.render.number('.', ',', 0, '') },           
            { "targets": [6, 7, 8, 9, 10, 11, 13], "render": numFormat()},

it`s as described, the value is correct e.g. 10281,71 but without thousands seperations, like in the other case:

            { "targets": [6, 7, 8, 9, 10, 11, 13], "render": numFormat},

it shows correct thousands seperator, but 10281,71 will be 10.281,00 again.

Any ideas ?

fahrstuhl65

@ Roland

ich habe das wie von dir beschrieben geändert - das ist mir wohl durchgerutscht, du hast natürlich Recht, erst die Punkte weg von tausendern, dann wechseln...danke !

rf1234

If you pass 10281,71 into $.fn.dataTable.render.number('.', ',', 2, '').display;
it won't work because the renderer will only render US numbers properly.

The comma will not be interpreted as a decimal point but rather as a misplaced thousand separator ... Excel has the same problem if you use the data tables Excel export. This is all completely US centric, bear that in mind please. Any INPUT to those functions (or to Excel) must comply with US standards. It is also advisable to get rid of all kinds of thousand separators before rendering and before making calculations.

The easiest solution is:
- Before rendering a number for display make sure it is "clean" in this format 1000000.98
- Before calculating with a number make sure you get rid of all the formatting and you have the "clean" format again 1000000.98

fahrstuhl65

@ Roland

ok - understood, i changed it as follow and now it works as expected - thanks a lot for your patience !

"columnDefs": [

            { "targets": [5], "render": $.fn.dataTable.render.number('.', ',', 0, '') },            

            {
                "targets": [6, 7, 8, 9, 10, 11, 13], "render": function (data, type, row) {

                    // make values US centric
                    data = data.toString().replace(/[\.]/g, "");
                    data = data.toString().replace(/[\,]/g, ".");

                    var numFormat = $.fn.dataTable.render.number('.', ',', 2, '').display;
                    return numFormat(data);
                } },
            
            {"targets": [11, 12, 13, 14, 17, 19, 23],
                "createdCell": function (td, cellData, rowData, row, col) {

                    // make values US centric
                    cellData = cellData.toString().replace(/[\.]/g, "");
                    cellData = cellData.toString().replace(/[\,]/g, ".");
                    
                    if (cellData < 0) {
                        $(td).css('color', 'red')
                    }
                    if (cellData >= 0) {
                        $(td).css('color', 'green')
                    }
                },
            }
        ],

rf1234

Glad you got it working!!

Many people run into this problem at one point. Mostly when they try to use the Excel export and their formatted numbers get messed up by Excel.

Roland