jQuery UI Widgets Forums Grid Grid aggregates accessing other column summary data

This topic contains 21 replies, has 3 voices, and was last updated by  assaf.frank123 10 years, 10 months ago.

Viewing 15 posts - 1 through 15 (of 22 total)
  • Author

  • assaf.frank123
    Participant

    Hi,

    My aggregates function would like to perform a calculation using the summary of 2 other columns that are aggregated
    individually already (sum aggregate) –

    for example – I have a cost column and an items column.Both have the Sum aggregates function.
    I would like in the third column aggregates (summary row) to show the sum of cost divided by the sum of items.

    Any idea how I can achieve this?

    Thanks.


    Dimitar
    Participant

    Hello assaf.frank123,

    In your third column we suggest you use Custom Aggregates. In the callback function, call the method getcolumnaggregateddata for the first and second columns. Do your calculation and return the result.

    Best Regards,
    Dimitar

    jQWidgets team
    http://www.jqwidgets.com/


    wfr
    Participant

    Hi,

    I have the very same problem, I would be interested how to formulate this task (since I’m a JavaScript novice).

    Thanks
    Walter


    Dimitar
    Participant

    Hello Walter,

    Here is an example. The “Product” aggregates show the sum of the aggregated sums of “Quantity” and “Price”:

    <!DOCTYPE html>
    <html lang="en">
    <head>
        <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
        <script type="text/javascript" src="../../scripts/jquery-1.10.2.min.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxgrid.aggregates.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script>
        <script type="text/javascript" src="../../scripts/demos.js"></script>
        <script type="text/javascript" src="generatedata.js"></script>
        <script type="text/javascript">
            $(document).ready(function () {
                // prepare the data
                var data = generatedata(200);
    
                var source =
                {
                    localdata: data,
                    datatype: "array",
                    datafields:
                    [
                        { name: 'firstname', type: 'string' },
                        { name: 'lastname', type: 'string' },
                        { name: 'productname', type: 'string' },
                        { name: 'quantity', type: 'number' },
                        { name: 'price', type: 'number' }
                    ],
                    updaterow: function (rowid, rowdata, commit) {
                        // synchronize with the server - send update command   
                        commit(true);
                    }
                };
    
                var dataAdapter = new $.jqx.dataAdapter(source);
    
                // initialize jqxGrid
                $("#jqxgrid").jqxGrid(
                {
                    width: 670,
                    source: dataAdapter,
                    showstatusbar: true,
                    statusbarheight: 25,
                    altrows: true,
                    showaggregates: true,
                    columns: [
                      { text: 'First Name', columntype: 'textbox', datafield: 'firstname', width: 90 },
                      { text: 'Last Name', datafield: 'lastname', columntype: 'textbox', width: 90 },
                      { text: 'Product', datafield: 'productname', width: 170, aggregates: [{ '<b>Total</b>':
                            function (aggregatedValue, currentValue, column, record) {
                                var sumQuantity = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'quantity', ['sum']);
                                var sumPrice = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'price', ['sum']);
                                return sumQuantity.sum + sumPrice.sum;
                            }
                      }]
                      },
                      { text: 'Quantity', datafield: 'quantity', width: 100, cellsalign: 'right', cellsformat: 'n2', aggregates: ["sum"] },
                      { text: 'Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2', aggregates: ["sum"] }
                      ]
                });
            });
        </script>
    </head>
    <body class='default'>
        <div id='jqxWidget'>
            <div id="jqxgrid">
            </div>
        </div>
    </body>
    </html>

    Best Regards,
    Dimitar

    jQWidgets team
    http://www.jqwidgets.com/


    wfr
    Participant

    Thanks a lot, Dimitar!

    It works, but I have still a small error.
    Positive numbers should be green, negative numbers should be red.

               
    { text: 'Bank-Wert', datafield: 'bank_wert', width: 150, cellsformat: 'F2', cellsalign: 'right', filtertype: 'textbox', aggregates: ['sum'],
    aggregatesrenderer: function (aggregates, column, element, summaryData) {
    var renderstring = "<div class='jqx-widget-content jqx-widget-content-" + theme + "' style='float: left; width: 100%; height: 100%;'>";
    $.each(aggregates, function (key, value) {
    var name = key == 'sum' ? 'Sum' : '' ;
    var color = 'red';
    if (key == 'sum' && summaryData['sum'] < 0) {
    color = 'red';
    }
    if (key == 'sum' && summaryData['sum'] > 0) {
    color = 'green';
    }
    renderstring += '<div style="color: ' + color + '; position: relative; margin: 4px; text-align: right; overflow: hidden;">' + name + ': ' + value + '</div>';
    });
    renderstring += "</div>";
    return renderstring;
    } },

    I guess it’s because of this line:
    var color = 'red';

    Can you help me to inplement the renderstring to this field (again: positive numbers = green, negative numbers = red):

    { text: 'Zahlung unterwegs an', datafield: 'zu_an', width: 180, columntype: 'textbox', filtertype: 'textbox', aggregates: [{ '<b>Total</b>' :function (aggregatedValue, currentValue, column, record) {
    var sumBank_wert = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'bank_wert', ['sum']);
    var sumZu_wert = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'zu_wert', ['sum']);
    return sumBank_wert.sum + sumZu_wert.sum;
    }
    }] },

    Thank you very much
    Walter


    Dimitar
    Participant

    Hello Walter,

    This should also be implemented in the custom aggregates callback function. Here is how to modify it (from the example we provided):

    { text: 'Product', datafield: 'productname', width: 170, aggregates: [{ '<b>Total</b>':
        function (aggregatedValue, currentValue, column, record) {
            var sumQuantity = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'quantity', ['sum']);
            var sumPrice = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'price', ['sum']);
            sum = sumQuantity.sum + sumPrice.sum
            var color = "green";
            if (sum < 0) {
                color = "red";
            };
            return '<span style="color: ' + color + ';">' + sum + '</span>';
        }
    }]
    },

    Best Regards,
    Dimitar

    jQWidgets team
    http://www.jqwidgets.com/


    wfr
    Participant

    Hi Dimitar!

    Thank you very much!
    I think there is a small error in this line:

    return '<span style= " color: ' + color + ';" >' + sum + '</span>';
    because the result is NaN (not a number?)

    When entering
    return sum
    the result is correctly displayed.

    Thank you
    Walter


    assaf.frank123
    Participant

    Great, the data is shown properly.

    The only thing – I wish not to display any label except the calculated value.

    Meaning, I removed the “‘<b>Total</b>'” but still get a “:” next to the value.

    How can I remove this?

    Thanks.


    assaf.frank123
    Participant

    OK, solved with an aggregatesrenderer function after that.


    wfr
    Participant

    Hm, I’m still not sure how to remove the Text AND the following “:”
    Furthermore, negative numbers should be red, positive numbers should be green.
    I get always green results.

    { text: 'Bank-Wert', datafield: 'bank_wert', width: 150, cellsformat: 'F2', cellsalign: 'right', filtertype: 'textbox', aggregates: ['sum'], aggregatesrenderer: function (aggregates, column, element, summaryData) 
    {
    var renderstring = "<div class='jqx-widget-content jqx-widget-content-" + theme + "' style='float: left; width: 100%; height: 100%;'>";
    							
    $.each(aggregates, function (key, value) 
    {
    var name = key == 'sum' ? 'Sum' : '' ;								
    var color = 'green';
    if (key == 'sum' && summaryData['sum'] < 0) 
    {
    color = 'red';
    renderstring += '<div style="color: ' + color + '; position: relative; margin: 4px; text-align: right; ">' + name + ': ' + value + '</div>';
    });
    renderstring += "</div>";
    return renderstring;
    } 
    },

    Cheers
    Walter


    Dimitar
    Participant

    Hello Walter,

    Is this your code of the “total” column? As I pointed earlier, the colouring of this column’s aggregates should be done in the aggregates callback function and not in aggregatesrenderer. As for other columns – colour the aggregates as done in the Aggregates Renderer demo.

    You can remove the colon by setting aggregatesrenderer after that:

    <!DOCTYPE html>
    <html lang="en">
    <head>
        <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
        <script type="text/javascript" src="../../scripts/jquery-1.10.2.min.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxgrid.aggregates.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script>
        <script type="text/javascript" src="../../scripts/demos.js"></script>
        <script type="text/javascript" src="generatedata.js"></script>
        <script type="text/javascript">
            $(document).ready(function () {
                // prepare the data
                var data = generatedata(200);
    
                var source =
                {
                    localdata: data,
                    datatype: "array",
                    datafields:
                    [
                        { name: 'firstname', type: 'string' },
                        { name: 'lastname', type: 'string' },
                        { name: 'productname', type: 'string' },
                        { name: 'quantity', type: 'number' },
                        { name: 'price', type: 'number' }
                    ],
                    updaterow: function (rowid, rowdata, commit) {
                        // synchronize with the server - send update command   
                        commit(true);
                    }
                };
    
                var dataAdapter = new $.jqx.dataAdapter(source);
    
                // initialize jqxGrid
                $("#jqxgrid").jqxGrid(
                {
                    width: 670,
                    source: dataAdapter,
                    showstatusbar: true,
                    statusbarheight: 25,
                    altrows: true,
                    showaggregates: true,
                    columns: [
                        { text: 'First Name', columntype: 'textbox', datafield: 'firstname', width: 90 },
                        { text: 'Last Name', datafield: 'lastname', columntype: 'textbox', width: 90 },
                        { text: 'Product', datafield: 'productname', width: 170, aggregates: [{ 'Total':
                            function (aggregatedValue, currentValue, column, record) {
                                var sumQuantity = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'quantity', ['sum']);
                                var sumPrice = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'price', ['sum']);
                                sum = sumQuantity.sum + sumPrice.sum
                                var color = "green";
                                if (sum < 0) {
                                    color = "red";
                                };
                                return '<span style="color: ' + color + ';">' + sum + '</span>';
                            }
                        }], aggregatesrenderer: function (aggregates, column, element) {
                            return aggregates.Total;
                        }
                        },
                        { text: 'Quantity', datafield: 'quantity', width: 100, cellsalign: 'right', cellsformat: 'n2', aggregates: ["sum"] },
                        { text: 'Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2', aggregates: ["sum"] }
                    ]
                });
            });
        </script>
    </head>
    <body class='default'>
        <div id='jqxWidget'>
            <div id="jqxgrid">
            </div>
        </div>
    </body>
    </html>

    Best Regards,
    Dimitar

    jQWidgets team
    http://www.jqwidgets.com/


    wfr
    Participant

    Hi Dimitar,

    I guess you mean this example:

    { text: 'Product', datafield: 'productname', width: 170, aggregates: [{ '<b>Total</b>':
        function (aggregatedValue, currentValue, column, record) {
            var sumQuantity = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'quantity', ['sum']);
            var sumPrice = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'price', ['sum']);
            sum = sumQuantity.sum + sumPrice.sum
            var color = "green";
            if (sum < 0) {
                color = "red";
            };
            return '<span style="color: ' + color + ';">' + sum + '</span>';
        }
    }]
    },

    I only need the sum of this field (no sum of 2 fields) and only the number without leading Text and “:” .

    Thank you
    Walter


    Dimitar
    Participant

    Hi Walter,

    I thought your issue was the same as assaf.frank123. Nevertheless, Here is another example (based on the demo Custom Aggregates):

    <!DOCTYPE html>
    <html lang="en">
    <head>
        <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
        <script type="text/javascript" src="../../scripts/jquery-1.10.2.min.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxgrid.aggregates.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script>
        <script type="text/javascript" src="../../scripts/demos.js"></script>
        <script type="text/javascript" src="generatedata.js"></script>
        <script type="text/javascript">
            $(document).ready(function () {
                // prepare the data
                var data = generatedata(200);
    
                var source =
                {
                    localdata: data,
                    datatype: "array",
                    datafields:
                    [
                        { name: 'firstname', type: 'string' },
                        { name: 'lastname', type: 'string' },
                        { name: 'productname', type: 'string' },
                        { name: 'quantity', type: 'number' },
                        { name: 'price', type: 'number' }
                    ],
                    updaterow: function (rowid, rowdata, commit) {
                        // synchronize with the server - send update command   
                        commit(true);
                    }
                };
    
                var dataAdapter = new $.jqx.dataAdapter(source);
    
                // initialize jqxGrid
                $("#jqxgrid").jqxGrid(
                {
                    width: 670,
                    source: dataAdapter,
                    showstatusbar: true,
                    statusbarheight: 25,
                    altrows: true,
                    showaggregates: true,
                    columns: [
                      { text: 'First Name', columntype: 'textbox', datafield: 'firstname', width: 90 },
                      { text: 'Last Name', datafield: 'lastname', columntype: 'textbox', width: 90 },
                      { text: 'Product', datafield: 'productname', width: 170 },
                      { text: 'Quantity', datafield: 'quantity', width: 100, cellsalign: 'right', cellsformat: 'n2' },
                      { text: 'Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2', aggregates: [{ 'Total':
                                function (aggregatedValue, currentValue, column, record) {
                                    var total = currentValue * parseInt(record['quantity']);
                                    return aggregatedValue + total;
                                }
                      }], aggregatesrenderer: function (aggregates, column, element) {
                          var color = "green"
                          if (aggregates.Total < 0) {
                              color = "red";
                          };
                          return '<span style="color: ' + color + ';">' + aggregates.Total + '</span>'
                      }
                      }
                    ]
                });
            });
        </script>
    </head>
    <body class='default'>
        <div id='jqxWidget'>
            <div id="jqxgrid">
            </div>
        </div>
    </body>
    </html>

    Best Regards,
    Dimitar

    jQWidgets team
    http://www.jqwidgets.com/


    assaf.frank123
    Participant

    Hi,

    After implementing both the custom aggregate function as well as an aggregate renderer function I see
    when a table with a few thousand rows – the browser crashes.
    both functions are called for every row.

    Isn’t there a way to do the same calculation only once ?

    Thanks..

    aggregates: [{ ”:
    function (aggregatedValue, currentValue, column, record) {
    var cost = $(“#jqxmachinesgrid”).jqxGrid(‘getcolumnaggregateddata’, ‘cost’, [‘sum’]);
    var leads = $(“#jqxmachinesgrid”).jqxGrid(‘getcolumnaggregateddata’, ‘numLeads’, [‘sum’]);
    if (leads.sum > 0) {
    return (cost.sum/leads.sum);
    } else {
    return ”;
    }
    }
    }],
    aggregatesrenderer: function (aggregates, column, element, summaryData) {
    var renderstring = “<div style=’font-size:11px;float: left; width: 100%; height: 100%;’>”;
    $.each(aggregates, function (key, value) {
    renderstring += ‘<div style=”position: relative; margin: 6px; text-align: left; overflow:
    hidden;”>’ + value + ‘</div>’;
    });
    renderstring += “</div>”;
    return renderstring;
    }


    Dimitar
    Participant

    Hi assaf.frank123,

    The solution I provided you is not suitable for so many rows. Here is another, faster, approach:

    aggregates: [{
        '': function (aggregatedValue, currentValue, column, record) {
            var total = parseInt(record['cost']) / parseInt(record['numLeads']);
            return aggregatedValue + total;
        }
    }]

    You get each row’s cell values through record, do your calculations, and add them to the aggregated value. Note that this may lead to a different result from the previous approach (unless you are summing the two cells).

    Best Regards,
    Dimitar

    jQWidgets team
    http://www.jqwidgets.com/

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic.