percentage between two column

percentage between two column

cmpluscmplus Posts: 65Questions: 13Answers: 0
edited March 2023 in Free community support

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>


Answers

  • kthorngrenkthorngren Posts: 21,325Questions: 26Answers: 4,949
    Answer ✓

    You can use columns.render to make calculations between cells in a row. See this example.

    Kevin

  • cmpluscmplus Posts: 65Questions: 13Answers: 0
    edited March 2023

    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

    columnDefs: [ {
                                targets: 5,
                                render: DataTable.render.percentBar( 'round','#FFF', '#269ABC', '#31B0D5', '#286090', 1, 'groove' )
                            }],
    
    NaN%
    
  • kthorngrenkthorngren Posts: 21,325Questions: 26Answers: 4,949
    Answer ✓

    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

  • cmpluscmplus Posts: 65Questions: 13Answers: 0
    edited March 2023

    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

  • kthorngrenkthorngren Posts: 21,325Questions: 26Answers: 4,949
    Answer ✓

    I updated the example to just show the percentage.
    https://live.datatables.net/seyuwoja/1/edit

    Kevin

  • cmpluscmplus Posts: 65Questions: 13Answers: 0
    edited March 2023

    I'm just using this, but to avoid this numbering problem? can it be solved? to see a shorter number

    96.84210526315789%
    
    6.052631578947368%
    
    99.86301369863014%
    

    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?

  • kthorngrenkthorngren Posts: 21,325Questions: 26Answers: 4,949
    Answer ✓

    Use Javascript toFixed() to format the number of decimal places.

    Kevin

  • cmpluscmplus Posts: 65Questions: 13Answers: 0

    thanks for your help

  • cmpluscmplus Posts: 65Questions: 13Answers: 0
    edited March 2023

    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  column 5     column 6
    11440   9.622.464     NaN%
    
    

    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?

  • rf1234rf1234 Posts: 2,990Questions: 87Answers: 421
    Answer ✓

    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".

  • cmpluscmplus Posts: 65Questions: 13Answers: 0
    edited March 2023

    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

    columnDefs: [
                                {
                                    targets: 3,
                                    render: function (data, type, row, meta) {
                                        return type === 'display'
                                        ? '<progress value="' + (100 * row[1] / row[2] ).toFixed(2) + '" max="100"></progress><span>' + (100 * row[1] / row[2] ).toFixed(2) + '%</span>'
                                        : data;
                                    },
                                }
                            ],
    
    Resut :
    557 516 107.95%
    992 0   Infinity%
    
    columnDefs: [
                                {
                                    targets: 3,
                                    render: function (data, type, row, meta) {
                                        return type === 'display'
                                        ? '<progress value="' + (100 * row[2] / row[1] ).toFixed(2) + '" max="100"></progress><span>' + (100 * row[2] / row[1] ).toFixed(2) + '%</span>'
                                        : data;
                                    },
                                }
                            ],
    
    Result:
    557 516 92.64%
    992 0   0.00%
    

  • kthorngrenkthorngren Posts: 21,325Questions: 26Answers: 4,949
    Answer ✓

    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

  • cmpluscmplus Posts: 65Questions: 13Answers: 0

    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.

  • kthorngrenkthorngren Posts: 21,325Questions: 26Answers: 4,949
    Answer ✓

    Yes, it can be difficult to discern between Datatables functionality and Javascript at times.

    Kevin

Sign In or Register to comment.