How can I sum values from column with two different css classes?

eiskarlsson · September 2022

Hello,

I have columns with two css classes .redLabel and .greenLabel. I would like to get two sums at the footer of the columns - one for the .redLabel and one for the .greenLabel.

Here is a row from the html table:
<td>
<span id="MainContent1_ServiceContractsControl1_rptCompanies_rptAmounts_9_labelAmount_0"
class="greenLabel" style="font-size: small">591,44</span>
</td>

Here is the datatables javascript:

    function pageLoad() {
    $('td').each(function () {
        if ($(this).html().replace(/\s+/, "") == '') {
            $(this).remove();
        }
    });
    $('tr').each(function () {
        if ($(this).html().replace(/\s+/, "") == '') {
            $(this).remove();
        }
    });
    $('#myTable').DataTable({
        scrollY: 800,
        scrollX: '100%',/*1200,*/
        scrollCollapse: true,
        //sScrollXInner: '150%',
        paging: false,
        fixedColumns: true,
        responsive: false,
        bSort: true,
        aaSorting: [],
        aoColumnDefs: [
        { aTargets: ['_all'], bSortable: true }],
        "footerCallback": function (settings, json) {
            this.api().columns('.sum', { search: 'applied' })
                .every(function () {
                var column = this;

                var sum = column//.cells('span.greenLabel').data()
                    .reduce(function (a, b) {
                        var x = parseFloat(a);
                        if (isNaN(x)) { x = $(a).html(); }
                        var y = parseFloat(b);
                        if (isNaN(y)) { y = $(b).html(); }
                        return parseFloat(x.toString().replace(',', '.')) + parseFloat(y.toString().replace(',', '.'));
                    }, 0);

                $(column.footer()).html(sum.toFixed(2));

            });
        }

    });


}

As of now it creates one common sum at the bottom of the column.
How can I create two different sums?
Any hep is greatly appreciated!

rf1234 · September 2022

Here is something from my own coding that does what you require. It makes two sums in the footer. The sums are made from formatted numbers that cannot be summed up but need to be converted first. Finally the sums need to be formatted in either English, UK or German, Germany formats - depending on the user language.

The columns to sum up are columns 3 and 6. That also explains the colspans in <tfoot>. If there is nothing to sum up there is no footer with the sums.

HTML:

<table id="tblSubOrder" class="table table-striped table-bordered"
       cellspacing="0" width="100%">
    <thead>
        <tr>
            <th colspan="5" class="bg-primary">Auftragsdaten</th>
            <th colspan="4" class="bg-danger">Zahlungsdaten</th>
        </tr>
        <tr>
            <th><?php echo $en?('Order Name'):('Auftragsbezeichnung');?></th>
            <th><?php echo $en?('Partner'):('Auftragnehmer');?></th>
            <th><?php echo $en?('Order Date'):('Auftragsdatum');?></th>
            <th class="dt-right"><?php echo $en?('Order Costs'):('Auftragskosten');?></th>
            <th><?php echo $en?('Additional Information'):('Zusatzinformation');?></th>
            <th class="orderPayments"><?php echo $en?('Payment Date'):('Zahlungsdatum');?></th>
            <th class="orderPayments dt-right"><?php echo $en?('Payment Amount'):('Zahlungsbetrag');?></th>
            <th class="orderPayments"><?php echo $en?('Voucher Number'):('Beleg-Nummer');?></th>
            <th class="orderPayments"><?php echo $en?('Additional Info'):('Zusatzinfo');?></th>
        </tr>
    </thead>      
    <tfoot>
        <tr class="bg-success subOrderFooter">
            <th colspan="3"></th>
            <th></th>
            <th colspan="2"></th>
            <th></th>
            <th colspan="2"></th>
        </tr>
    </tfoot>
</table>

JS

footerCallback: function (row, data, start, end, display) {                
    var api = this.api(); 
    if ( api.rows( {search: "applied"} ).count() < 1 ) {
        $(".subOrderFooter").addClass("hidden");
        return;
    } else {
        $(".subOrderFooter").removeClass("hidden");
    }
    if (lang == 'de') {
        var numberRenderer = $.fn.dataTable.render.number( '.', ',', 2 ).display;
    } else {
        var numberRenderer = $.fn.dataTable.render.number( ',', '.', 2 ).display;
    }

    // Remove the formatting to get float data for summation
    var floatVal = function (i) {
        if (lang == 'de') {
            return typeof i === 'string' ? i.replace(/[\.]/g, '').replace(/[\,]/g, '.') * 1 : typeof i === 'number' ? i : 0;
        } else {
            return typeof i === 'string' ? i.replace(/[\,]/g, '') * 1 : typeof i === 'number' ? i : 0;
        }
    };

    // Total over all pages
    var twoDecPlacesCols = [3, 6];
    var total;
    var curr = data[0].ctr.currency;
    for ( i=0; i < twoDecPlacesCols.length; i++ ) {
        total = api
            .column(twoDecPlacesCols[i])
            .data()
            .reduce(function (a, b) {
                return floatVal(a) + floatVal(b);
            }, 0);
        // Update footer
        $(api.column(twoDecPlacesCols[i]).footer()).html( numberRenderer(total) + " " + curr );
    }
}

Looks like this:

allan · September 2022

You are going to need some way to identify the two columns - the column index would do:

var dt = this.api();
var col3Cells = dt.cells(null, 3).nodes().toArray();

col3Sum = col3Cells.reduce( ... );

And the same for column index 4, or whatever it is you want to use.

Allan

eiskarlsson · September 2022

Thank you all for the answers. They are helpful, however it seems I was not clear in asking my question. Each column has cells, which have either css class - .redLabel or .greenLabel. I would like to get two sums for each column - one for the red and one for the green. I apologize for the unclarity.

rf1234 · September 2022

Each column has cells, which have either css class - .redLabel or .greenLabel. I would like to get two sums for each column - one for the red and one for the green. I apologize for the unclarity.

And what difference does this make? Regardless of what CSS class you are using you can do the sum up.

Or are you saying those colors are alternating WITHIN one column? If that is so you can do the accumulation by simply looping through the cells of each column.

eiskarlsson · September 2022

Yes, these colors are alternating randomly within one column. I would like to do the accumulation by looping through the cells of each color.

rf1234 · September 2022

You could use columns().every() or rows().every() as iterators
and do something with "this.data()" to access the value of the cell or "this.nodes()" to figure out the cell color.

https://datatables.net/reference/api/columns().every()

https://datatables.net/reference/api/rows().every()

eiskarlsson · September 2022

Thank you all for the answers. I found this thread on your site: https://datatables.net/forums/discussion/71194/conditional-total-sum-in-certain-column-in-footer and ended up doing the following:

<script type="text/javascript">


function pageLoad() {
    $('td').each(function () {
        if ($(this).html().replace(/\s+/, "") == '') {
            $(this).remove();
        }
    });
    $('tr').each(function () {
        if ($(this).html().replace(/\s+/, "") == '') {
            $(this).remove();
        }
    });

    $('#myTable').DataTable({
        scrollY: 800,
        scrollX: '100%',/*1200,*/
        scrollCollapse: true,
        //sScrollXInner: '150%',
        paging: false,
        fixedColumns: true,
        responsive: false,
        bSort: true,
        aaSorting: [],
        aoColumnDefs: [
            { aTargets: ['_all'], bSortable: true }],
        "footerCallback": function (settings, json) {
            var api = this.api();
            this.api().columns('.sum', { search: 'applied' })
                .every(function () {
                    var column = this;

                    var totalGreen = 0.0;
                    var totalRed = 0.0;
                    var totalViolet = 0.0;


                    api.columns('.sum', { search: 'applied' })
                        .cells(null, column.index(), { search: 'applied' })
                        .nodes()
                        .each(function (n) {
                            if ($(n).find("span")
                                .hasClass('greenLabel')) {
                                totalGreen += parseFloat($(n)
                                    .text().replace(',', '.'));
                            }
                            if ($(n).find("span")
                                .hasClass('violetLabel')) {
                                totalViolet += parseFloat($(n)
                                    .text().replace(',', '.'));
                            }
                            if ($(n).find("span")
                                .hasClass('redLabel')) {
                                totalRed += parseFloat($(n)
                                    .text().replace(',', '.'));
                            }
                        })


                    $(column.footer())
                        .html(
                            'Green: ' + totalGreen.toFixed(2) + '<br/>' +
                            'Violet: ' + totalViolet.toFixed(2));
             });
        }
    });
};


</script>

This achieves the desired result. I appreciate your help.

Best wishes!

How can I sum values from column with two different css classes?

How can I sum values from column with two different css classes?

Answers

Howdy, Stranger!

Categories

DataTables

How can I sum values from column with two different css classes?

How can I sum values from column with two different css classes?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables