S.Clancy
S.Clancy

Reputation: 65

DataTables - how to get the percentage of a column in html table

I am having a difficult time in calculating in getting the percentage for a column in my html table for datatables.

enter image description here

In the table above, I have figured out how to get the sum of each column, but for "Column D" totals, I need to get the percentage which will be "(total of column B / total of column C) * 100. Also column F will be (total of column E / total of column A) instead of the sum which is shows now.

The total for column D is blank because the javascript code that I used, could not sum up column D because I am using a percent sign. So with my current code, I want to sum for column A,B,C,E,G and the percent for column D, and column F would be column E divide by column A.

How can I go about to calculate these totals using Datatables and javascript? Any help will be appreciated. Thank you.

$(document).ready(function() {
    // DataTable initialisation
    $('table.off-table1').DataTable({

            "searching": false,
            "info": false,
            "paging": false,
            "autoWidth": true,
            "footerCallback": function ( row, data, start, end, display ) {
                var api = this.api();
                nb_cols = api.columns().nodes().length;
                var j = 1;
                while(j < nb_cols){
                    var pageTotal = api
                .column( j, { page: 'current'} )
                .data()
                .reduce( function (a, b) {
                    return Number(a) + Number(b);
                }, 0 );
          // Update footer
          $( api.column( j ).footer() ).html(pageTotal);
                    j++;
                }
            }
        
        });
});
<head>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jszip-2.5.0/dt-1.10.21/b-1.6.2/b-colvis-1.6.2/b-html5-1.6.2/b-print-1.6.2/cr-1.5.2/fc-3.3.1/kt-2.5.2/r-2.2.5/rg-1.1.2/rr-1.2.7/sp-1.1.1/sl-1.3.1/datatables.min.css"/>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/jszip-2.5.0/dt-1.10.21/b-1.6.2/b-colvis-1.6.2/b-html5-1.6.2/b-print-1.6.2/cr-1.5.2/fc-3.3.1/kt-2.5.2/r-2.2.5/rg-1.1.2/rr-1.2.7/sp-1.1.1/sl-1.3.1/datatables.min.js"></script>


<style>

</style>

</head>



<table id="def-table1" class="def-table1" cellspacing="0" width="100%">
                            <thead>
                                <tr>
                                    <th scope="col">ID</th>
                                    <th scope="col">A</th>
                                    <th scope="col">B</th>
                                    <th scope="col">C</th>
                                    <th scope="col">D%</th>
                                    <th scope="col">E</th>
                                    <th scope="col">F</th>
                                    <th scope="col">G</th>
                                </tr>
                            </thead>
                            <tfoot>
                                <tr>
                                    <td>Totals</td>
                                    <td></td>
                                    <td></td>
                                    <td></td>
                                    <td></td>
                                    <td></td>
                                    <td></td>
                                    <td></td>
                                </tr>
                            </tfoot>
                            <tbody>
                                <tr>
                                    <td>1</td>
                                    <td class="minutes">41</td>
                                    <td class="minutes">14</td>
                                    <td class="minutes">33</td>
                                    <td class="minutes">42.4 %</td>
                                    <td class="minutes">42</td>
                                    <td class="minutes">1.02</td>
                                    <td class="minutes">8</td>
                                </tr>
                                <tr>
                                    <td>2</td>
                                    <td class="minutes">8</td>
                                    <td class="minutes">2</td>
                                    <td class="minutes">6</td>
                                    <td class="minutes">33.3 %</td>
                                    <td class="minutes">5</td>
                                    <td class="minutes">0.63</td>
                                    <td class="minutes">1</td>
                                </tr>
                                <tr>
                                    <td>3</td>
                                    <td class="minutes">29</td>
                                    <td class="minutes">11</td>
                                    <td class="minutes">25</td>
                                    <td class="minutes">44.0 %</td>
                                    <td class="minutes">33</td>
                                    <td class="minutes">1.14</td>
                                    <td class="minutes">5</td>
                                </tr>
                                <tr>
                                    <td>4</td>
                                    <td class="minutes">7</td>
                                    <td class="minutes">1</td>
                                    <td class="minutes">5</td>
                                    <td class="minutes">20.0 %</td>
                                    <td class="minutes">5</td>
                                    <td class="minutes">0.71</td>
                                    <td class="minutes">1</td>
                                </tr>
                            </tbody>
                            </table>

Upvotes: 0

Views: 794

Answers (1)

charlietfl
charlietfl

Reputation: 171669

I simplified this down by not using this.api() and only using the arguments passed to the callback and some basic array methods. Should be easier to work with now

function tfootTotals(tfRow, data, start, end, display) {
    // display is array of data indices that are included in this view
    const pageData = data.filter((arr, i) => display.includes(i));
    // create array of column totals
    const totals = Array.from(pageData[0]).fill(0);
    pageData.forEach(arr => arr.forEach((e, i) => totals[i] += (+e) || 0));

    // calculate the special ones
    totals[4] = (100 * totals[2] / totals[3]).toFixed(1) + '%';
    totals[6] = totals[5] / totals[1];

    // set the tfoot cell text. slice ignores first one 
    // so indexing is one less than totals array
    $(tfRow.cells).slice(1).text(i => totals[i+1])
    // console.log(totals)
}
  
  
  // DataTable initialisation
  $('#def-table1').DataTable({
    "searching": false,
    "info": false,
    "paging": false,
    "autoWidth": true,
    "footerCallback": tfootTotals
  });
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.24/css/jquery.dataTables.css">

<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.js"></script>

  <table id="def-table1" class="def-table1" cellspacing="0" width="100%">
  <thead>
    <tr>
      <th scope="col">ID</th>
      <th scope="col">A</th>
      <th scope="col">B</th>
      <th scope="col">C</th>
      <th scope="col" class="col-d">D%</th>
      <th scope="col">E</th>
      <th scope="col" class="col-f">F</th>
      <th scope="col">G</th>
    </tr>
  </thead>
  <tfoot>
    <tr>
      <td>Totals</td>
      <td></td>
      <td></td>
      <td></td>
      <td>ff</td>
      <td></td>
      <td></td>
      <td></td>
    </tr>
  </tfoot>
  <tbody>
    <tr>
      <td>1</td>
      <td class="minutes">41</td>
      <td class="minutes">14</td>
      <td class="minutes">33</td>
      <td class="minutes">42.4 %</td>
      <td class="minutes">42</td>
      <td class="minutes">1.02</td>
      <td class="minutes">8</td>
    </tr>
    <tr>
      <td>2</td>
      <td class="minutes">8</td>
      <td class="minutes">2</td>
      <td class="minutes">6</td>
      <td class="minutes">33.3 %</td>
      <td class="minutes">5</td>
      <td class="minutes">0.63</td>
      <td class="minutes">1</td>
    </tr>
    <tr>
      <td>3</td>
      <td class="minutes">29</td>
      <td class="minutes">11</td>
      <td class="minutes">25</td>
      <td class="minutes">44.0 %</td>
      <td class="minutes">33</td>
      <td class="minutes">1.14</td>
      <td class="minutes">5</td>
    </tr>
    <tr>
      <td>4</td>
      <td class="minutes">7</td>
      <td class="minutes">1</td>
      <td class="minutes">5</td>
      <td class="minutes">20.0 %</td>
      <td class="minutes">5</td>
      <td class="minutes">0.71</td>
      <td class="minutes">1</td>
    </tr>
  </tbody>
</table>

Upvotes: 1

Related Questions