sum a column based on a condition in another column

GusBeare

Hi,

Firstly wow! hasn't DataTables come along since the last time I used it! Amazing work. It's brilliant!

Now my question.

My data represents income and expenses in a table. Each row can either be income or expense. So I would like to sum the income and sum the expenses. I have set up the plugin and can sum the current page or all the pages.

However, my data looks like this:

Amount	Direction
123.44	income
34.55	expense
233.5	expense

And this works to sum all the data in Amount:

 var sum = table.column(0).data().sum();
        alert(sum);

But I need a sum for all rows where the nth row is 'income' or 'expense'.

Is this possible and can anyone explain how?

TIA
Gus

GusBeare

I worked it out with this code which adds a button. Click the button to see some totals.

 $('<button type="button" class="btn btn-info">Show Filtered Totals</button>')
            .prependTo('#btnInOut')
            .on('click', function() {

                var Table = $('#expenses').dataTable();
                var data = Table._('tr', { "filter": "applied" });
         
                var incomeVal = 0;
                var expenseVal = 0;
            
                // add up the totals for income
                for (var i = 0; i < data.length; i++) {

                    if (data[i][5] == "income")
                        incomeVal += Number(data[i][1]);
                    else {
                        expenseVal += Number(data[i][1]);
                    }
                }
                var IncomeMsg = "<p class=\"running\">Income Total = <strong>&#163;" + incomeVal.toFixed(2) + "</strong></p>";
                var ExpensesMsg = "<p class=\"running\">Expenses Total = <strong>&#163;" + expenseVal.toFixed(2) + "</strong></p>";

                $(".running").remove();  // remove any previous totals
                
                $(IncomeMsg).appendTo(".totals");  // add in the new ones
                $(ExpensesMsg).appendTo(".totals");

        });

allan

Hi Gus,

Thanks for updating your thread - good to hear you have a method to do what you want.

I was wondering if the code could be reduced a little, and think that using the selectors with their new function ability (from 1.10.3+) might reduce the LOC slightly. For example:

var sum = table
    .cells( function ( index, data, node ) {
        return table.row( index ).data()[1] === 'income' ?
            true : false;
    }, 0, { search: 'applied' } )
    .data()
    .sum();

So we are using the cells() method and passing in the first parameter (the row selector) as a function - it will decide if the row is included or not based on the data logic check. Second parameter is the column for the cell and third is to get only the filtered rows, matching what you have above.

Allan

GusBeare

thanks Allan

that is a lot more compact. I will try it out.

cheers
Gus

GusBeare

I can't get your code to work at all

var table = $('#expenses').dataTable();
                var sum = table.cells(function (index, data, node) {
                    return table.row(index).data()[5] === 'income' ?
                        true : false;
                }, 0, { search: 'applied' })
                .data()
                .sum();

I get "Uncaught type error: undefined is not a function" error on this line:

```js var sum = table.cells(function (index, data, node) {

Any ideas?

allan

var table = $('#expenses').dataTable();

Should be:

 var table = $('#expenses').DataTable();

See the FAQs ;-)

Allan

GusBeare

ah yes!

"Very likely you are using a jQuery object rather than a DataTables API instance. The form $().dataTable() will return a jQuery object, while $().DataTable() returns a DataTables API instance. "