How to calculate sum of a custom value

Question

I have a table of products like this:

As I mentioned in the image, I have specified a field called VAT which stands for Value Added Tax and I need the sum of all VAT fields to be showed on page with jQuery, so here is my try:

var total_price =  parseInt($('#total-price').html().replace(/\,/g,''));
$('#total-price').html(parseInt(total_price + (price*cnt)).toLocaleString());

var total_discount =  parseInt($('#total-discount').html().replace(/\,/g,''));
$('#total-discount').html(parseInt(total_discount + (discount*cnt)).toLocaleString());
                
var total_final =  parseInt($('#total-final').html().replace(/\,/g,''));
$('#total-final').html(parseInt(total_final + (price_final*cnt)).toLocaleString());

var total_vat =  parseInt($('#total-vat').html().replace(/\,/g,''));
$('#total-vat').html(parseInt(total_final + (price_final*cnt) + value_added_tax).toLocaleString());

And the result of these variables goes here:

But now the problem is sum of vats returns the same value of total-final. So instead of 12,350,00 it should be showing 13,286,000 (because the sum of value_added_tax must be added to total_final).

And the line that does this calculation goes here:

$('#total-vat').html(parseInt(total_final + (price_final*cnt) + value_added_tax).toLocaleString());

Something is missing here, or I'm doing it in a wrong way. So if you know how to calculate properly the final price of a product (total_final + value_added_tax), please let me know...

I would really appreciate any idea or suggestion from you guys...

Thanks in advance.

Answer 1

you can achieve it very simply

function calculateTotalOfAColum(){
   var columnHashMap = {};
   $('#products tr').each(function(){

       $(this).find('td').each(function(i,el){
                     var price = Number(String($(el).text()).replace(/,/g,''))
           if(!columnHashMap.hasOwnProperty(i)){          columnHashMap[i] = {};
        columnHashMap[i] = price;
      }else{
        columnHashMap[i] += price;
      }
        });

   });
  return columnHashMap;
}

$(function(){
   var totalObj = calculateTotalOfAColum();
   console.log(Object.values(totalObj)) // total amounts
})

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<table id="products" class="products">
    <tr>
      <th>Final</th>
      <th>VAT</th>
      <th>-</th>
      <th>Price</th>
      <th>After Discount</th>
      <th>Discount</th>
      <th>Unit Price</th>
      <th>Quantity</th>
    </tr>
    <tr>
      <td>3,924,000</td>
      <td>324,000</td>
      <td>0</td>
      <td>3,600,000</td>
      <td>1,800,000</td>
      <td>0</td>
      <td>1,800,000</td>
      <td>2</td>
    </tr>
    <tr>
      <td>1,526,000</td>
      <td>126,000</td>
      <td>0</td>
      <td>1,400,000</td>
      <td>1,400,000</td>
      <td>0</td>
      <td>1,400,000</td>
      <td>1</td>
    </tr>
    <tr>
      <td>5,886,000</td>
      <td>486,000</td>
      <td>0</td>
      <td>5,400,000</td>
      <td>1,800,000</td>
      <td>0</td>
      <td>1,800,000</td>
      <td>3</td>
    </tr>
    <tr>
      <td>1,950,000</td>
      <td>0</td>
      <td>0</td>
      <td>1,950,000</td>
      <td>1,950,000</td>
      <td>0</td>
      <td>1,950,000</td>
      <td>1</td>
    </tr>
  </table>

Answer 2

Dynamic Column Adding

When you run the snippet, it will show an identical table to your image and tally up the totals. The 'price' total will be the tally of the price columns with the VAT added to it. In case the order of columns changes, the indexes are defined in the first lines of code.

I have shown a way to dynamically add up the columns on any size table with any number of rows. The only hard-coded meta-data was in establishing that column one is the price total and column 2 is the vat to be added to column 1 in the totals. I'd be happy to explain any of the code if you'd like.

You'll notice that the loop ignores the first tr because it has no <td>'s (it uses <th>) - but your table might not be set up that way, and/or you may not have a header row atop the data. You can uncomment out the if (row_index!==0){ part if you do need the first row to be counted.

Please take a look and let me know if this needs adjusting.

const rows = $("#table tr"),
finalIndex = 0, // column 1 is the Final
vatIndex = 1, // column 2 is the VAT
priceIndex = 3 // column 4 is the Price
let tally = [];
rows.each(function(row_index) {
  $(this).find('td').each(function(col_index) {
    if (row_index == rows.length - 1) {
      // get totals
      if (col_index == finalIndex) $(this).html((tally[priceIndex] + tally[vatIndex]).toLocaleString())
      else $(this).html(tally[col_index].toLocaleString())
    } else {
      // if (row_index != 0) {
      tally[col_index] = (tally[col_index] || 0) + (Number($(this).text().replace(/,/g, '')) || 0);
      // }
    }
  })
})

#table {
  border: 1px solid #ccc;
}

#table td,
#table th {
  border-bottom: 1px solid #ccc;
  border-left: 1px solid #ccc;
  padding: 10px;
  font-family: "Arial";
  color: #666;
  text-align: center;
}

#table td:first-child,
#table th:first-child {
  border-left: none;
}

tr#totals td {
  border-top: 2px solid #333;
  ;
  color: #000;
  font-weight: bold;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<table id='table' cellspacing='0'>
<tr><th>Final</th><th>VAT</th><th>-</th><th>Price</th><th>-</th><th>-</th><th>-</th><th>-</th></tr>
<tr><td>3,924,000</td><td>324,000</td><td>0</td><td>3,600,000</td><td>1,800,000</td><td>0</td><td>1,800,000</td><td>2</td></tr>
<tr><td>1,526,000</td><td>126,000</td><td>0</td><td>1,400,000</td><td>1,400,000</td><td>0</td><td>1,400,000</td><td>1</td></tr>
<tr><td>5,886,000</td><td>486,000</td><td>0</td><td>5,400,000</td><td>1,800,000</td><td>0</td><td>1,800,000</td><td>3</td></tr>
<tr><td>1,950,000</td><td>0</td><td>0</td><td>1,950,000</td><td>1,950,000</td><td>00</td><td>1,950,000</td><td>1</td></tr>
<tr id='totals'><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
</table>

Answer 3

The question wasn't so clear. It would have been easier, if you would have shared your base HTML snippet which contained the table.

Anyway, presenting my solution to calculate the sum of data from table columns.

FYI: Assuming the column names are as shown in screenshot below:

$(function(){
    // Function that reads the table data and returns array of objects
    const readTableDataToArray = (tableId) => {
      let fields = [];
      let products = [];

      $(tableId).find('tr').each((index, row) => {
        if (index === 0) {
          $(row).find('th').each((thIndex, header) => {
            fields.splice(thIndex, 1, `${thIndex + 1}_${$(header).text()}`);
          });
        } else {
          let productRowValue = {};
          $(row).find('td').each((cellIndex, cell) => {
            productRowValue = {
              ...productRowValue,
              [fields[cellIndex]]: parseFloat(($(cell).text() || '').replace(/[^0-9]+/g, ''), 10),
            };
          });

          products.push(productRowValue);
        }
      });

      return products;
    }

    // Function that takes in array of objects and returns sum of all fields
    const getSumOfColumns = (data) => {
      return data.reduce((accumalator, value) => {
        const keys = Object.keys(value);
        let tempAcc = {};

        keys.map((key) => {
          const accFieldValue = accumalator[key] || 0;
          const keyFieldValue = value[key] || 0;

          tempAcc[key] = accFieldValue + keyFieldValue;
        });

        return tempAcc;
      }, {});;
    }

    // Function that adds row to table with the sum data provided
    const addTotalRowToTable = (tableId, data) => {
      const sumRow = document.createElement('tr');
      sumRow.classList.add('sum-row');
      const columns = Object.keys(data);

      const values = columns.map((col) => {
        const cell = document.createElement('td');
        cell.innerHTML = data[col];

        return cell;
      });

      sumRow.append(...values);

      $(tableId).append(sumRow);
    }


    // Trigger function to get products array
    const products = readTableDataToArray('#products');

    // Calculate the sum for fields
    const sum = getSumOfColumns(products);

    // Append sum row to table
    addTotalRowToTable('#products', sum);
  });

* {
      font-family: Arial, Helvetica, sans-serif;
    }
    .products,
    .products th,
    .products td {
      border: 1px solid #d7d8d7;
      border-collapse: collapse;
    }
    .products th,
    .products td {
      padding: 4px 8px;
      min-width: 100px;
    }
    .products td {
      text-align: right;
    }

    tr.sum-row {
      background: #d7d8d7;
    }

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<table id="products" class="products">
    <tr>
      <th>Final</th>
      <th>VAT</th>
      <th>-</th>
      <th>Price</th>
      <th>After Discount</th>
      <th>Discount</th>
      <th>Unit Price</th>
      <th>Quantity</th>
    </tr>
    <tr>
      <td>3,924,000</td>
      <td>324,000</td>
      <td>0</td>
      <td>3,600,000</td>
      <td>1,800,000</td>
      <td>0</td>
      <td>1,800,000</td>
      <td>2</td>
    </tr>
    <tr>
      <td>1,526,000</td>
      <td>126,000</td>
      <td>0</td>
      <td>1,400,000</td>
      <td>1,400,000</td>
      <td>0</td>
      <td>1,400,000</td>
      <td>1</td>
    </tr>
    <tr>
      <td>5,886,000</td>
      <td>486,000</td>
      <td>0</td>
      <td>5,400,000</td>
      <td>1,800,000</td>
      <td>0</td>
      <td>1,800,000</td>
      <td>3</td>
    </tr>
    <tr>
      <td>1,950,000</td>
      <td>0</td>
      <td>0</td>
      <td>1,950,000</td>
      <td>1,950,000</td>
      <td>0</td>
      <td>1,950,000</td>
      <td>1</td>
    </tr>
  </table>

Answer 4

Reading data from HTML is very slow, a better approach would be to also store the data in an array and then read the values from that array to calculate VAT. Once you have the number you can then use jQuery to write it to the page.