Total for certain category only

andysk

Hi, as on the link http://datatables.net/release-datatables/examples/advanced_init/footer_callback.html to get total amount of all rows. How to achieve total per position so it can look like below? :
Total Software Engineer : 999999999
Total Accountant : 999999
etc.

ThomD

I needed to do a bit more than just a sum (avg, min, max), so I ended up looping through each visible row to do my calculations. I used this to loop in the footerCallback.

var api = this.api(), data;
api.rows({ page: 'current'}).eq( 0 ).each( function (idx) {
    var thisRow = api.row( idx );
    [do some math on each row, I used a switch function to get 
     my grouping by a field value ]
});

andysk

Hi Thom...
Is that a way to do filter like

api.rows(column3.value = 'Accountant')
.column( 4)
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );

btw.... how to format in good reading format like in your post

ThomD

To highlight code snippets, use three back ticks before and after the code on newlines

There are lots of ways to do what you want. I usually use the less efficient, but easier to read looping and switch functions. So, in my example I'm doing totals based on a column called Rating, totaling up a column called MeritPercentage. A bit of that looks like this.

api.rows({ page: 'current'}).eq( 0 ).each( function (idx) {
    var thisRow = api.row( idx );
    switch( thisRow.data().Rating) {
        case "NI":
            NICounter++;
            NITotal += thisRow.data().MeritPercentage;
            break;
        case "ME":
            MECounter++;
            METotal += thisRow.data().MeritPercentage;
etc

ThomD

I played with this some more to see what I could do with the API to do the filtering and totals. This first part (API and IntVal) are straight from the example on the footer Callback.

NITotal is the sum of all the values in column 6 (Current Base Pay), based on the Rating column = "NI". This does the same thing as looping through all the rows looking for "NI" values.

Note that the sequence of the chain (api->column -> rows -> data -> reduce) does seem to matter. I'm still learning about how to chain effectively, so that may be normal.

footerCallback: function ( row, data, start, end, display ) {
    var api = this.api(), data;
    // Remove the formatting to get integer data for summation
    var intVal = function ( i ) {
        return typeof i === 'string' ?
            i.replace(/[\$,]/g, '')*1 :
            typeof i === 'number' ?
                i : 0;
    };
    NITotal = api
        .column( 6, { page: 'current'} )
        .rows( function ( idx, rowData, rowNode ) {
            return rowData.Rating === 'NI' ? true : false;
        } )                 
        .data()
        .reduce( function (runningTotal, currentRow) {
            return intVal(runningTotal) + intVal(currentRow.CurrentBase);
        },0 );              
}       

andysk

Hi Thom... Thanks a lot for sharing the idea of highlighting code snippets by using three back ticks before and after the code on newlines.

Also thanks for the code above. Despite the (.column) become useless, your code (currentRow) actually will become the rowData itself, hence currentRow[x] will return the column data that I want.

Furthermore, your code indirectly shows me a better way to solve my problem, as I need to sum more than 1 column. I change your last code a bit to store the summary on the array :

api
        .rows( function ( idx, rowData, rowNode ) {
            if (rowData[2] === 'Accountant')
            {
                for(var a=5; a<TotalColumn; a++){
                        sumArray[a] += intVal(rowData[a]);
                }
                return true;
            }
            else  {
                return false;
            } 
        } );  

then after above code, for each array, the program will generate <td>sumArray[a]</td>

Note : I believe my code is still dirty, however, it works for now. Somebody may be able to show me more clean code.

ThomD

Glad it worked out for you. You are right, might .column parameter probably isn't needed since I'm getting the column value with the reduce command. I would need to move the { page: 'current'} selector to another place.

andysk

Btw.... how to mark answer? I can't find the button or link. I can mark answer before like in my previous thread. http://datatables.net/forums/discussion/29116/row-height#latest .

or how to close this thread? which option I should use when making new post(question or discussion)?