Divide aggregate field sum by another aggregate field sum

[Dominik]

Hello, it is possible to make calculations such as:

Z = X / Y;

For example I have 3 aggregate columns:

X Y Z

Value of X = 1; groupFooterTemplate = Average

Value of Y = 6; groupFooterTemplate = Sum

Value of Aggregate column Z should be like 1/6= 0,16;

Example:

So for Z column I want to have value from custom calculation (X/Y). 16/62 = 0,258 = ~26%

[Dominik]

Any ideas? Please help me :(

[Nithya Sivaprakasam]

Hi Dominik,

Greetings from Syncfusion support.

Query:” Divide aggregate field by another aggregate field”

We can achieve your requirement by using a custom aggregate function. In that function, you can do your own aggregate function. To use custom aggregation, specify the type as Custom, and provide the custom aggregate function in the customAggregate property in the e-grid-aggregates tag helper.

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" allowPaging="true">

    <e-grid-aggregates>

        <e-grid-aggregate>

            <e-aggregate-columns>

                <e-aggregate-column field="UnitsInStock" type="Custom" footerTemplate=" Custom function (Z): ${Custom}" customAggregate="@("customAggregateFn")"></e-aggregate-column>

            </e-aggregate-columns>

        </e-grid-aggregate>

    </e-grid-aggregates>

    <e-grid-pagesettings pageCount="5"></e-grid-pagesettings>

    <e-grid-columns>

        <e-grid-column >……….</e-grid-column>

    </e-grid-columns>

</ejs-grid>

 

<script>

    function customAggregateFn() {

          gridData = grid.dataSource; // gridObject.dataSource returns entire data if the grid having local dataSource

  var totalSum = 0;

  // do your calculation as you want with the entire dataset

  for (var i = 0; i < <any>gridData.length; i++) {

    totalSum = totalSum + gridData[i]['UnitsInStock'];

  }

  let average = Math.round(totalSum / gridData.length);

  // return the calculated value

  return average;

    }

</script>

#Screenshot:

For more information, kindly check the below documentation.

Documentation: https://ej2.syncfusion.com/aspnetcore/documentation/grid/aggregates/custom-aggregate

Please get back to us if you need further assistance on this.

Regards,

Nithya Sivaprakasam.

[Dominik]

Hello thank you for response, I think I understand your example, but the problem is I need to do calculation not on whole data set, just I have to do calculations based on grouped values for example:

Screenshot:

So my green Custom Aggregate should be result of divination Pink/Blue values. But for each grouped aggregate not for whole data set. (Green value = Pink/Blue)

Thank you for your time and have a nice day!

[Pavithra Subramaniyam]

Hi Dominik,

You can make the calculation inside the custom aggregate function for each group using the “data.aggregate” property form the argument list. Please refer to the below code example for more information.

 <e-aggregate-column field="ColZ" type="Custom" groupFooterTemplate="Average/Sum: ${Custom}" customAggregate="@("customAggregateFn")"></e-aggregate-column>

 

 

function customAggregateFn (data, aggColumn) {

  return data.aggregates['ColY - average'] / data.aggregates['ColX - sum'];

};

 

Please get back to us if you need further assistance on this.

Regards,

Pavithra S

[Dominik]

Thank you very much for your assistance, It works as expected. 

You are great! Thank you for your help yet again :)

[Pavithra Subramaniyam]

Hi Dominik,

We are glad to hear that the provided solution works!

Please get back to us if you need further assistance on this.

Regards,

Pavithra S