percentage between two column
percentage between two column
cmplus
Posts: 65Questions: 13Answers: 0
Hello I have this scenario, in this table I have numbers, I would like to create the percentage between two numbers in the neighboring cell, for example I would like in column E to see the percentage of B and C in column F the percentage of D and H, not I can enter the percentage in the cell can anyone help me?
A B C D E F G H
2022 8124 4723 12847 21.347.943
2021 7721 3719 11440 9.622.464
Totale 15845 8442 24287 53.3 % 65.2 % 34.8 % 0
<table id="def-table1" class="def-table1" cellspacing="0" width="100%">
<thead>
<tr>
<th scope="col">A</th>
<th scope="col">B</th>
<th scope="col">C</th>
<th scope="col">D</th>
<th scope="col" class="col-d">E</th>
<th scope="col" class="col-f">F</th>
<th scope="col" class="col-g">G</th>
<th scope="col">H</th>
</tr>
</thead>
<tfoot>
<tr>
<td>Total</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tfoot>
<tbody>
<tr>
<td>2021</td>
<td>{{ $ticket2021 }}</td>
<td">{{ $ticketE2021 }}</td>
<td>{{ $ticket20211 }}</td>
<td>percentage between B and C</td>
<td>percentage between D and H</td>
<td></td>
<td>9.622.464</td>
</tr>
<tr>
<td>2022</td>
<td>{{ $ticket2022 }}</td>
<td>{{ $ticketE2022 }}</td>
<td>{{ $ticket20221 }}</td>
<td>percentage between B and C</td>
<td>percentage between D and H</td>
<td></td>
<td>21.347.943</td>
</tr>
</tbody>
</table>
<script type="text/javascript" src="https://cdn.datatables.net/plug-ins/1.11.5/api/sum().js"></script>
<script>
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[1]).toFixed(1) + ' %';
totals[5] = (100 * totals[1] / totals[3]).toFixed(1) + ' %';
totals[6] = (100 * totals[2] / totals[3]).toFixed(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>
This question has accepted answers - jump to:
Answers
You can use
columns.render
to make calculations between cells in a row. See this example.Kevin
I'm trying to insert with this, for now I've only managed to insert the bar but I still can't insert the values to see the right percentage inside the cell
The percentBar plugin won't work for your case since you want to use it on a calculated column. It would work with columns B, C or D. One option is to use the progress bar as shown in this example. I built a simple example showing this progress bar with the percent shown to the right using
columns.render
.https://live.datatables.net/fosudova/1/edit
Kevin
For me it's a new thing, I've never done it before and therefore I don't know where to put my hands, yes it's true I need to see the percentage between two cells and not the progress bar, I'm looking for a way to be able to do it but for now i'm on the high seas, i managed to do the total calculation at the bottom, now it seems i'm figuring out to insert a progress bar, but i haven't found what i need yet
I am trying your example, thanks for the help you are giving me
I updated the example to just show the percentage.
https://live.datatables.net/seyuwoja/1/edit
Kevin
I'm just using this, but to avoid this numbering problem? can it be solved? to see a shorter number
also in your example it does, so it depends on the size of the number, can something be done to not make it so long?
Use Javascript toFixed() to format the number of decimal places.
Kevin
thanks for your help
Now I fixed the percentage number and your code is perfect, but I don't understand why when I go to use it on two columns containing large numbers it returns a NaN% value
column 4 and column 5 have these value if i apply in column 6 the code returns a NaN%
This happens because of the . of separation that I entered, can this be avoided? leaving the . separators or you can not?
Yes, it can be avoided. Just do the calculation with unformatted numbers. 9.622.464 is NOT a number. 9622464 is a number. 9,622,464 is not a number either while 9622464.99 is a number and 9622464,99 is not a number.
I changed the example to work with two different number formats which I called "German" (de) and "English" (en).
https://live.datatables.net/bugofujo/1/edit
"German" is like your format 9.622.464 and "English" would be 9,622,464. I also make sure that the resulting percentage is either formatted "German" or "English".
thanks for the answer, but if I want to insert the bar with the percentage number inside instead of lateral I tried to modify but I don't see it inside or it disappears or lateral
The progress bar you are using is a standard HTML element, not a Datatables element, documented here which doesn't have built-in options to allow for embedding the percentage in the bar. This SO thread has suggestions of how to do this.
Or if you are using Bootstrap you could use their progress bar. See this tutorial. There may be other alternatives you can use.
Kevin
now I have clearer ideas, I use bootstrap and now I understand how to integrate the bar as I want it, thanks, sometimes you get lost in little things.
Yes, it can be difficult to discern between Datatables functionality and Javascript at times.
Kevin