Trying to get the number of rows so I can calculate an average for the footer using the footerCallba

msimmons

I'm sure this is very simple but I cannot find it...

"footerCallback": function( tfoot, data, start, end, display ) {
    var api = this.api();

    var theColumnTotal = api
        .column( 4 )
        .data()
        .reduce( function (a, b) {
            if(isNaN(a)){
                return '';
            } else {
                a = parseFloat(a);
            }
            if(isNaN(b)){ 
                return '';
            } else {
                b = parseFloat(b);
            }
            return (a + b).toFixed(2);
        }, 0 );

    // Update footer
    $( api.column( 4 ).footer() ).html(
        theColumnTotal / [NEED ROW COUNT HERE]
    );
}

Let me know if there is any further info needed, thanks!

vincmeister

Hi there,

have you try length ?

allan

I'd suggest using the count() method:

"footerCallback": function( tfoot, data, start, end, display ) {
    var api = this.api();
 
    var columnData = api
        .column( 4 )
        .data();

    var theColumnTotal = columnData
        .reduce( function (a, b) {
            if(isNaN(a)){
                return '';
            } else {
                a = parseFloat(a);
            }
            if(isNaN(b)){
                return '';
            } else {
                b = parseFloat(b);
            }
            return (a + b).toFixed(2);
        }, 0 );
 
    // Update footer
    $( api.column( 4 ).footer() ).html(
        theColumnTotal / columnData.count()
    );
}

Allan

msimmons

Thanks Allan, that is perfect!

prcwest

Hello

I use the same code and it is working perfect for my needs.
i stuck because i have 8 columns. how it would look the code with multiple columns.
Any help would be appreciated.

colin

Hi @prcwest ,

When the number 4 is in that example, you can just change to be any column.

Cheers,

Colin

prcwest

Thank you for your response but i would need all columns at the same time.
like 1, 2, 3 .....until 8

colin

Just create a loop that goes through the columns - something like this. That example is creating dropdowns on each column, you would do similar but create your totals.

C

prcwest

Hello Colin

Thank you for your response. Im trying to include the columns().every() function but i cant work it out. Could you maybe give me an example on the code below. I am very far from an expert. Your help would be appreciated.

Best regards
Pascal

$('#dtcactushrs').DataTable( {
        responsive: true,
        "footerCallback": function( tfoot, data, start, end, display ) {
            var api = this.api();

            var columnData = api
                .column( 1 )
                .data();

            var theColumnTotal = columnData
                .reduce( function (a, b) {
                    if(isNaN(a)){
                        return '';
                    } else {
                        a = parseFloat(a);
                    }
                    if(isNaN(b)){
                        return '';
                    } else {
                        b = parseFloat(b);
                    }
                    return (a + b).toFixed(2);
                }, 0 );

            // Update footer
            $( api.column( 1 ).footer() ).html(
                theColumnTotal / columnData.count() + ('hrs. ave.')
            );
        }

    } );

colin

Easiest way would be something like:

$('#dtcactushrs').DataTable( {
        responsive: true,
        "footerCallback": function( tfoot, data, start, end, display ) {
            var api = this.api();

for (var i = 0; i < api.columns().count(); i++ ){

            var columnData = api
                .column( i )
                .data();

            var theColumnTotal = columnData
                .reduce( function (a, b) {
                    if(isNaN(a)){
                        return '';
                    } else {
                        a = parseFloat(a);
                    }
                    if(isNaN(b)){
                        return '';
                    } else {
                        b = parseFloat(b);
                    }
                    return (a + b).toFixed(2);
                }, 0 );

            // Update footer
            $( api.column( i ).footer() ).html(
                theColumnTotal / columnData.count() + ('hrs. ave.')
            );
        }
}
    } );

prcwest

Hello Colin

Thank you very much.I really appericate your help. That's working for all columns.I would have another question. I'm trying to get the average number from the displayed table including total but the total is not working. not on the first page and the second is complete out. Later on i would like to work with a date range and i want to calculate the average for the chosen date but still want to see the overall number

I prepared a fiddle maybe you could have another look please?

$('#dtcuidadhrs').DataTable({
    responsive: true,
    "footerCallback": function(tfoot, data, start, end, display) {
        var api = this.api();

        for (var i = 0; i < api.columns().count(); i++) {

            var columnData = api
                .column(i)
                .data();
            var theColumnTotal = columnData
                .reduce(function(a, b) {
                    if (isNaN(a)) {
                        return '';
                    } else {
                        a = parseFloat(a);
                    }
                    if (isNaN(b)) {
                        return '';
                    } else {
                        b = parseFloat(b);
                    }
                    return (a + b).toFixed(2);
                }, 0);

            var columnData = api
               .column( i, { page: 'current'} )
               .data()
            var theColumnPage = columnData
                .reduce(function(a, b) {
                    if (isNaN(a)) {
                        return '';
                    } else {
                        a = parseFloat(a);
                    }
                    if (isNaN(b)) {
                        return '';
                    } else {
                        b = parseFloat(b);
                    }
                    return (a + b).toFixed(2);
                }, 0);

            // Update footer
            $( api.column( 0 ).footer() ).html('Avarage');
            $(api.column(i).footer()).html(
                theColumnPage / columnData.count() + ' ('+ theColumnTotal / columnData.count() +' Total)'
            );
        }
    }
});

colin

Hi @prcwest ,

On your line 47 above, you're using columnData twice - but I suspect these should be separate variables, since the totals are summed in different places higher up.

Averaging dates would be easy if you convert them to unix time first. Moment.js is a good place to go for any date/time based JS operations.

Cheers,

Colin

prcwest

Hello @colin

You are correct. I changed my columnData in total to columnDataTotal and it works perfect. Thank you very much for all your help.