sum filtered rows/columns

longestdrive

Hi
I want to be able to sum a column of data after a filter has been applied using the plug in column filter.

I found this thread: http://datatables.net/forums/discussion/2053/fnfootercallback-sum-column-after-filter/p1

Which partly relates to the solution but I'm unclear on how I can use the object created:

._('td:nth-child(4)', {"filter": "applied"})

I'm a novice at jquery - I assume I need to go through each cell in the column and add the value but I havent a clue on how to start this

Can you point me in the right direction please

Thanks

GregP

To be honest, I don't know what the "filter" part will do (and I've never actually used the underscore function at all!), but in general what you're ideally getting back is an array of values related to the selector. So, in your example, you will get an array of values for your 4th column, presumably filtered with the keyword "applied".

If the original data is stored in integer or float form, you can then iterate through it like any other array to get a sum:

[code]
function sumColumn(col, filterString) {
var dataArray = $('#myTable')._('td:nth-child(col)', {"filter": filterString});
var sum = 0;
var sumCell = $('td#sum');

for (var i=0, len=dataArray.length; i < len; i++) {
sum += +dataArray[i];
}

sumCell.text(sum);
}
[/code]

I've broken it out into a semi-generic function. It's not super-optimized or anything... you'll want to add your own sanity checks and try/catches as needed. Here's what it does, if you call it with sumColumn(4,"applied"):

- Calls the underscore function on a dataTable with the ID "myTable", looking in column 4 with the filter "applied" and stores the results (an array) in the variable dataArray.
- Initializes the variable "sum"
- caches a jQuery object which selects a TD with the ID "sum". I don't know if you have this. You'll modify the selector accordingly!
- iterates through the array and adds to sum. The "+var" utility converts dataArray[i] to a number if it can. You'll probably want better sanity checks.
- takes the result of sum and uses jQuery to push it into the sumCell

mel

I have successfully used this function as follows:
[code]function sumColumn() {
var dataArray = oTable._('td:nth-child(4)', {"filter": "applied"});
var sum = 0;
for (var i=0, len=dataArray.length; i < len; i++) {
sum += +dataArray[i];
}
$("table.display tfoot th:nth-child(4)").html(sum);
}
[/code]
calling it from a button:
[code]sumaColumna [/code]

I'm working on server-side mode , but this function works fine on the client side so it saves a lot of calls to the server

allan

Looks good. The fun news is that in v.1.10 with the new API you will be able to do a column sum as simply as: `table.column( i ).data().sum();` ( `sum` might be a plug-in function, I've not quite decided yet...).

Allan

marcel

Hi Allan

Is there any possibility wth the current 1.9.4 release to access just the filtered rows while using the bDeferRender option using a JSON source without server side search involved?

I'm using bDeferRender for performance issues and would like to go over/process the filtered rows in order to count and calculate average values (on filtered rows) and display them in the footer. This works great as long as bDeferRender is false, as otherwise the above mentioned function is only able to 'find' the already existing DOM rows which could be just the ones from the first page.

Is there any current solution on how to get the filtered values not from the DOM, but from DataTables? Will this be taken care of in the upcoming v 1.10 release ?

Thanks a lot for your help

Marcel

allan

Sounds like you want the fnGetData method to get the data? That will also be a whole heck of a lot after than reading from the DOM.

Allan

marcel

I experimented with fnGetData before, but this seems to return only data for rows that are already rendered in the DOM. Please correct me if I'm wrong.

I was hoping to have some kind of way to get the row numbers for the ones filtered, so I could go over aaData and make calculations on the values of the filtered rows.

Using "bDeferRender = true" I currently don't see any possibility on how I can access ALL the filtered values if the corresponding rows haven't been rendered yet.

Not sure how DataTables works internally (such as if filtering is only readable after the rows have been rendered) or if there's any other way in retrieving the filtered rows? I would just need some kind of way that returns a list like 1,7,55,999 are filtered row results from aaData, no matter if they have been rendered yet or not.

Thanks
Marcel