How to sum multiple columns fitered by custom filter?

markzzz

Hi there!
I'm summing some columns where I place the class sum this way:

var dataTableMultipleColumnsSum = function () {
    var api = this.api();
    api.columns('.dum').every(function () {
        var total = api
            .column( this.index() )
            .data()
            .reduce(function (a, b) {
                return ParseNumber(a) + ParseNumber(b);
            }, 0);

        $(this.footer()).html(total + " total")"
        );
    });
}

But, once I filter the rows using a custom filter:

$.fn.dataTable.ext.search.push(
    function (settings, data, dataIndex) {
        var columnFilterID = 1;
        var selectedClinic = parseInt($('.dataTables_customToolbar select').val());
        var dataClinicID = parseInt(data[columnFilterID]) || 0;
        if (isNaN(selectedClinic) || dataClinicID == selectedClinic) {
            return true;
        }

        return false;
    }
);

I can still see the total of all rows, not the ones being filtered, for each column.

How would you achieve this?
Thanks

colin

Hi @markzzz ,

Easily done, just change the column() line to be

column( this.index(), {search: 'applied'} )

Cheers,

Colin

markzzz

I see, thanks! What's the difference between that and:

this.index(), { filter: "applied" }

It seems both works

colin

filter is just old&inferior - search is new&improved and should be used in preference. This page here gives a bit more info

C

markzzz

Thanks, it works perfect!
Another question colin (if you can): what if I would like to do some "sum" only if another column (at the same row) is "true"? Tried this:

var dataTableMultipleColumnsSum = function () {
    var api = this.api();
    api.columns('.sum').every(function () {
        var sum = api.column(this.index(), { search: "applied" })
            .cells(function (index, data, node) {
                return grid.row(index).data().PaymentCaseStudyIsPaid === true ? true : false;
            }, 0, { search: 'applied' })
            .data().reduce(function (a, b) { 
                console.log(b); 
                return ParseNumber(a) + ParseNumber(b); 
            }, 0);

        // ...
    });
}

But it returns weird stuff. The external column is at position [8], and its called PaymentCaseStudyIsPaid (which contains true, false, or null).

Where am I wrong?
Thanks so much

markzzz

Another attempt:

var sum = api.column(this.index(), { search: 'applied' })
    .data()
    .reduce(function (a, b, index) {
        var amountToAdd = 0;

        if (grid.row(index).data().PaymentCaseStudyIsPaid) {
            amountToAdd = ParseNumber(b);
        }

        return ParseNumber(a) + amountToAdd;
    }, 0);

But once filtered, it consider the index of all rows, not after the filter applied.

colin

Hi @markzzz ,

Take a look at this example here - this is only counting the search results if the city is 'London', so similar to yours. If you search for 'Developer', it's easy to see.

Hope that helps,

Cheers,

Colin

markzzz

Hi @colin,

uhm not really the same. Here you have single columns. I need to iterate for all columns that have the .sum class. So its NxM operation, without passing the column ID.

But I think I could easily use the this.index() in the top, and than use that index.
But I'm not sure I can access to var d = this.data(); using array []. In fact d[index] give me error. I need d.NameParameter. Any clues?

colin

Use can use a jQuery expression to get the columns that match a class - so something like

table.column($('.sum')).data()

Cheers,

Colin

markzzz

You misunderstood me.
Here's the code I have:

var dataTableMultipleColumnsSum = function () {
    var api = this.api();
    api.columns('.sum').every(function () {
        var columnIndex = this.index();

        var totalAmount = api.column(columnIndex, { search: 'applied' })
            .data()
            .reduce(function (a, b, index) {
                return ParseNumber(a) + ParseNumber(b);
            }, 0);

        var paidAmount = 0;
        api.rows({ search: "applied" }).every(function (rowIdx, tableLoop, rowLoop) {
            var row = this.data();
            if (row[columnIndex + 1] === true) {
                paidAmount += ParseNumber(row[columnIndex1]);
            }
        });

        $(this.footer()).html(
            FormatDataTableNumber(paidAmount).replace(" €", "") + "/" + FormatDataTableNumber(totalAmount)
        );
    });
}

It should work. But I can't access to row[columnIndex1 (or row[columnIndex + 1]) since var row = this.data(); return an object, not an array.
Tried .toArray(), but it says its not a function.

Quite strange, because in your example var d = this.data(); returns an array

markzzz

P.s: I'm retrieving data with Ajax:

ajax: {
    url: "/Activities/GridLoadData",
    type: "POST",
    datatype: "json"
}

Here's my JSON:

{  
   "data":[  
      {  
         "ID":1,
         "Date":"15/05/2018",
         "ClinicID":5,
         "Clinic":"Dente Pollo",
         "Patient":"Ale Fritz",
         "TreatmentType":"FULL+ACC",
         "PaymentMethod":"Acconto + PagoDIL",
         "Estimate":4100.99,
         "PaymentCaseStudyAmount":249.99,
         "PaymentCaseStudyIsPaid":true,
         "ActivityStatus":2
      },
      {  
         "ID":2,
         "Date":null,
         "ClinicID":6,
         "Clinic":"Dentisti Associati",
         "Patient":"Alessandro Tutan",
         "TreatmentType":"BITE",
         "PaymentMethod":"Acconto + PagoDIL",
         "Estimate":null,
         "PaymentCaseStudyAmount":null,
         "PaymentCaseStudyIsPaid":null,
         "ActivityStatus":1
      },
      {  
         "ID":3,
         "Date":"02/05/2018",
         "ClinicID":7,
         "Clinic":"ADI S.r.L.",
         "Patient":"Alessandro Pollissimo",
         "TreatmentType":"FISSA",
         "PaymentMethod":"Anticipato",
         "Estimate":1200.99,
         "PaymentCaseStudyAmount":null,
         "PaymentCaseStudyIsPaid":null,
         "ActivityStatus":1
      },
      {  
         "ID":4,
         "Date":"24/05/2018",
         "ClinicID":8,
         "Clinic":"Dentisti \u0026 Bisturi",
         "Patient":"Mario Rossiccio",
         "TreatmentType":"FULL",
         "PaymentMethod":"Anticipato",
         "Estimate":4000.00,
         "PaymentCaseStudyAmount":null,
         "PaymentCaseStudyIsPaid":null,
         "ActivityStatus":1
      },
      {  
         "ID":5,
         "Date":null,
         "ClinicID":9,
         "Clinic":"Dentè \u0026 Got",
         "Patient":"Paul Beccalossi",
         "TreatmentType":"FULL+ACC",
         "PaymentMethod":"Acconto + PagoDIL",
         "Estimate":null,
         "PaymentCaseStudyAmount":null,
         "PaymentCaseStudyIsPaid":null,
         "ActivityStatus":1
      },
      {  
         "ID":6,
         "Date":null,
         "ClinicID":5,
         "Clinic":"Dente Pollo",
         "Patient":"Massimiliano Luibello",
         "TreatmentType":"FULL",
         "PaymentMethod":"Acconto",
         "Estimate":null,
         "PaymentCaseStudyAmount":null,
         "PaymentCaseStudyIsPaid":null,
         "ActivityStatus":1
      }
   ],
   "recordsFiltered":6,
   "recordsTotal":6
}

Can be that? Not sure what should I do. Convert object to arrays? Uhm...

colin

Hi,

Take a look at this example, it's similar to that last one, but this time it's using objects as you are. Here, I've named (columns.name) each of the columns, which you can then access in the sums:

                .rows( { search: "applied" } ).every( function ( rowIdx, tableLoop, rowLoop ) {
                  var d = this.data();
                  if (d.office === 'London') {
                    conditionalTotal += intVal(d.extn);
                  }

This should work for you, unless I'm being daft here.

Cheers,

Colin

markzzz

That's the way I did using .data().PaymentCaseStudyIsPaid in the example above.

But I need dynamic ID, I can't pass the name of the param I want, because must be dynamic and work for every kind of column.

It would change for each column (and the next of it, which is invisible and contain "flag" value).

I'm trying with somethings like this:

var columnsSettings = api.settings().init().columns;
...
if (row[columnsSettings[columnIndex + 1].name] === true) {
    paidAmount += ParseNumber(row[columnsSettings[columnIndex].name]);
}   

but I don't feel elegant at all. I need to call columnsSettings every time and such...
Any others idea? Not sure if I can retrieve the column's name in other ways.

Maybe init a global object on init?

initComplete: function (settings, json) {
    columnsSettings = settings.oInit.columns;
}

The fact is that this will execute after the column inits, so in callback like render this will fail at the beginning.

allan

Hi,

The key here is going to be the selector for getting only the rows that you want the data for to sum. There are a few ways to do that such as using a row-selector as a function or using the filter() method - let's go with a row selector first:

api
  .cells(
    function(idx, data, node) {
      return data.PaymentCaseStudyIsPaid ? true : false;
    },
    columnIndex,
    { search: "applied" }
  )
  .data()
  .reduce(function(a, b, index) {
    return ParseNumber(a) + ParseNumber(b);
  }, 0);

With filter():

var rowIndexes = api
  .rows({ search: "applied" })
  .indexes()
  .filter(function(idx) {
    return api.row(idx).data().PaymentCaseStudyIsPaid ? true : false;
  });

var sum = api
  .cells(rowIndexes, columnIndex)
  .data()
  .reduce(function(a, b, index) {
    return ParseNumber(a) + ParseNumber(b);
  }, 0);

I don't think there will be any noticeable difference in performance between them.

Allan