sum a column based on a condition in another column

sum a column based on a condition in another column

GusBeareGusBeare Posts: 11Questions: 4Answers: 0
edited November 2014 in Free community support

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

Answers

  • GusBeareGusBeare Posts: 11Questions: 4Answers: 0
    edited November 2014

    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"); });
  • allanallan Posts: 63,522Questions: 1Answers: 10,473 Site admin
    edited November 2014

    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

  • GusBeareGusBeare Posts: 11Questions: 4Answers: 0

    thanks Allan

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

    cheers
    Gus

  • GusBeareGusBeare Posts: 11Questions: 4Answers: 0

    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?

  • allanallan Posts: 63,522Questions: 1Answers: 10,473 Site admin
    edited November 2014

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

    Should be:

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

    See the FAQs ;-)

    Allan

  • GusBeareGusBeare Posts: 11Questions: 4Answers: 0

    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. "

This discussion has been closed.