My table has nested rows in which the first rows should have the sum of the values of their children.
How can I insert the sum of months into their respective quarters, and also insert the sum of the quarters into their respective years?
Here's a jsfiddle with the expected result and what I have tried.
Here's another jsfiddle with an attempt to solve this using classes.
$('table thead th').each(function(i) {
i = 1;
calculateColumn(i);
});
function calculateColumn(index) {
var totalColumn = 0;
$('table tr').each(function() {
var num = $('td', this).eq(index).text();
if (!isNaN(num) && num.length !== 0) {
totalColumn = parseInt(num);
}
});
$('table tfoot td').eq(index).html(totalColumn.toString());
}
table {
width: 75%;
}
td {
text-align: center;
}
.year-one {
background-color: lightgray;
}
.font-bold {
font-weight: bold;
}
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<table border="1">
<thead>
<tr>
<th></th>
<th>Column A</th>
<th>Expected Result</th>
</tr>
</thead>
<tbody>
<tr >
<td>2022</td>
<td></td>
<td>12</td>
</tr>
<tr >
<td>Total Q1</td>
<td></td>
<td>3</td>
</tr>
<tr >
<td>Jan</td>
<td>2</td>
<td></td>
</tr>
<tr >
<td>Mar</td>
<td>1</td>
<td></td>
</tr>
<tr >
<td>Total Q2</td>
<td></td>
<td>9</td>
</tr>
<tr >
<td>May</td>
<td>9</td>
<td></td>
</tr>
<tr >
<td>2021</td>
<td></td>
<td>15</td>
</tr>
<tr>
<td>Total Q1</td>
<td></td>
<td>8</td>
</tr>
<tr>
<td>Jan</td>
<td>2</td>
<td></td>
</tr>
<tr>
<td>Mar</td>
<td>6</td>
<td></td>
</tr>
<tr>
<td>Total Q2</td>
<td></td>
<td>7</td>
</tr>
<tr>
<td>May</td>
<td>7</td>
<td></td>
</tr>
</tbody>
<tfoot>
<tr >
<td>Total</td>
<td></td>
<td></td>
</tr>
</tfoot>
</table>
CodePudding user response:
Here is a working version
I rely on Total Q
in the first column, but a class might be safer like I did with year
$('table thead th').each(function(i) {
i = 1;
calculateColumn(i);
});
function calculateColumn(index) {
var totalColumn = 0;
let totalCell;
let yearCell;
$('table tr').each(function(i) {
if($(this).find("td").eq(0).hasClass('year')) yearCell = $(this).find("td").eq(index);
if($(this).find("td").eq(0).text().includes("Total Q")) totalCell = $(this).find("td").eq(index);
var num = $('td', this).eq(index).text();
if (!isNaN(num) && num.length !== 0) {
num = num;
yearCell.text( yearCell.text() num)
totalCell.text( totalCell.text() num)
totalColumn = num;
}
});
$('table tfoot td').eq(index).html(totalColumn);
}
table {
width: 75%;
}
td {
text-align: center;
}
.year-one {
background-color: lightgray;
}
.font-bold {
font-weight: bold;
}
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<table border="1">
<thead>
<tr>
<th></th>
<th>Column A</th>
<th>Column B</th>
</tr>
</thead>
<tbody>
<tr >
<td >2022</td>
<td></td>
<td></td>
</tr>
<tr >
<td>Total Q1</td>
<td></td>
<td></td>
</tr>
<tr >
<td>Jan</td>
<td>2</td>
<td>3</td>
</tr>
<tr >
<td>Mar</td>
<td>1</td>
<td>4</td>
</tr>
<tr >
<td>Total Q2</td>
<td></td>
<td></td>
</tr>
<tr >
<td>May</td>
<td>9</td>
<td>11</td>
</tr>
<tr >
<td >2021</td>
<td></td>
<td></td>
</tr>
<tr>
<td>Total Q1</td>
<td></td>
<td></td>
</tr>
<tr>
<td>Jan</td>
<td>2</td>
<td>6</td>
</tr>
<tr>
<td>Mar</td>
<td>6</td>
<td>7</td>
</tr>
<tr>
<td>Total Q2</td>
<td></td>
<td></td>
</tr>
<tr>
<td>May</td>
<td>7</td>
<td>3</td>
</tr>
</tbody>
<tfoot>
<tr >
<td>Total</td>
<td></td>
<td></td>
</tr>
</tfoot>
</table>