Aggregate average that excludes DataItem = 0

[Bill]

Is there any functionality or does anyone have a workaround so that when a grid aggregate average is calculated, it does not include those items that have a "zero" value.   The field being aggregated is a of type "double" and cannot be null or empty.   However, zero is basically treated in this grid as "user hasn't enter a value yet".   Any help appreciated.

[Alex Hajigeorgieva]

Hello, Bill,

To fully understand the scenario, I would need to gather some more information from you. Such as the controller and the grid definition, as well as the expected result.

If there is a column with values 1.5, 0.0, 0.0, the result would be 1.5 regardless of how many others may have 0.0 as a value. So can you please elaborate perhaps with a small example of what is the expected outcome?

The Kendo UI DataSource for ASP.NET Core AggregateResults are calculated with the help of the Aggregate() method which iterates over the entire data that was passed to it:

https://docs.microsoft.com/en-us/dotnet/api/system.linq.enumerable.aggregate?view=netcore-2.2#System_Linq_Enumerable_Aggregate__1_System_Collections_Generic_IEnumerable___0__System_Func___0___0___0__

Look forward to hearing back from you.

Kind Regards,
Alex Hajigeorgieva
Progress Telerik

[Bill]

Hi Alex,

I'm sure the root issue is my misunderstanding of the aggregate() method but the average of 1.5, 0 and 0 is most definitely not 1.5 and would certainly be impacted by additional zeroes (I am speaking mathematically, not the aggregate method).  The "desired" outcome is actually to have 1.5, 0,0 to produce an average of 1.5 because the two zero values, in my case, are really "no answer provided by user", not a 'value' of zero.   When I run the code (snippets below) I get a correct calculation of the mathematical average for the BoundItem "ItemValue".  Unfortunately, what my request/post is asking for is a way to NOT include those dataitems that have an "ItemValue"  = 0.

Here is the controller read action:

public ActionResult AssessmentValue_Read(int? id, [DataSourceRequest]DataSourceRequest request)

{

    {

        SiteAssessmentValueVM assessmentvalues = new SiteAssessmentValueVM();

        var data = (

            from sav in _context.SiteAssessmentValue

            join sa in _context.SiteAssessment on sav.AssessmentId equals sa.Id

            join s in _context.Site on sa.SiteId equals s.Id

            join c in _context.Country on s.CountryId equals c.Id

            join r in _context.Region on c.RegionId equals r.Id

            join oas in _context.OutcomeAttributeScales on sav.ItemId equals oas.Id

            join oat in _context.OutcomeAttributes on oas.AttributeId equals oat.Id

            join oa in _context.OutcomeAreas on oat.OutcomeAreaId equals oa.Id

                                

            select new

            {

                sav.Id,

                sav.ItemValue,

                sav.Comment,

                sav.ItemId,

                AssessmentId = sa.Id,

                oas.Active,

                oa.OutcomeName,

                oat.AttributeName,

                sav.Description,

                r.OrganizationId

            }).ToList();

 

        List<SiteAssessmentValueVM> assessmentList = data.Select(x => new SiteAssessmentValueVM

        {

            Id = x.Id,

            AssessmentId = x.AssessmentId,

            ScaleActive = Convert.ToBoolean(x.Active),

            ItemId = x.ItemId,

            ItemValue = x.ItemValue,

            AttributeName = x.AttributeName,

            OutcomeName = x.OutcomeName,

            Comment = x.Comment,

            Description = x.Description,

            OrganizationId = x.OrganizationId

        }).Where(x => x.OrganizationId == HttpContext.Session.GetInt32("curOrgId")

        && x.AssessmentId == id && x.ScaleActive == true).Distinct().ToList();

 

        DataSourceResult result = assessmentList.ToDataSourceResult(request);

        return Json(result);

    }

}

 

Here is the grid

<div class="col-xs-18 col-md-12">
        @(Html.Kendo().Grid<ICPCore.Models.ViewModels.SiteAssessmentValueVM>
                ()
                .Name("grid")
                .Events(events =>
                {
                    events.DataBound("onDataBound");
                })
                .Columns(columns =>
                {
                    columns.Bound(p => p.Id).Hidden();
                    columns.Bound(p => p.ItemId).Hidden();
                    columns.Bound(p => p.Description).Width(400);
                    columns.Bound(p => p.ItemValue).Width(100)
                        .ClientGroupHeaderColumnTemplate("Avg: #=kendo.toString(average, \"n2\")#");
                    columns.Bound(p => p.Comment);
                    columns.Bound(p => p.OutcomeName)
                        .ClientGroupHeaderColumnTemplate("OutcomeName")
                        .Hidden();
                    columns.Bound(p => p.AttributeName)
                        .ClientGroupHeaderColumnTemplate("AttributeName")
                        .Hidden();
                })
                .ToolBar(toolbar =>
                {
                    toolbar.Save();
                })
                .Editable(editable => editable.Mode(GridEditMode.InCell))
                .Navigatable()
                .Scrollable()
                .HtmlAttributes(new { style = "height:550px;" })
                .AutoBind(true)
                .DataSource(dataSource => dataSource
                    .Ajax()
                    .Batch(true)
                    .ServerOperation(false)
                    .Aggregates(aggregates=>
                    {
                        aggregates.Add(a => a.ItemValue).Average();
                    })
                    .Group(groups =>
                    {
                        groups.Add(g => g.OutcomeName);
                        groups.Add(g => g.AttributeName);
                    })
                    .Events(events =>
                    {
                        events.Error("error_handler");
                    })
                    .Model(model =>
                    {
                        model.Id(p => p.Id);
                        model.Field(p => p.ItemId).Editable(false);
                        model.Field(p => p.Description).Editable(false);
                        model.Field(p => p.OutcomeName).Editable(false);
                        model.Field(p => p.AttributeName).Editable(false);
                    })
                    .PageSize(100)
                    .Update("AssessmentValue_Update", "SiteAssessmentValues")
                    .Read(read => read.Action("AssessmentValue_Read", "SiteAssessmentValues").Data("id"))
                )
                .Pageable()
                .Resizable(resize => resize.Columns(true))
        )
    </div>

[Alex Hajigeorgieva]

Hi, Bill,

Thank you for the provided snippet. It demonstrates that there are grouping and batch editing enabled and that the server operations are disabled. This means that all the aggregating is done on the client and you have all of the data available too.

If you wish to exclude the data items with zero values from the calculations, you could define the ClientGroupTemplate as a function and calculate the average programmatically.

1) One way would be to use the data items that are available as arguments in the template function and aggregate them recursively

2) Or you could create another data source instance and filter out the values with 0 and use the aggregated result:

* example code which should work with the groups that are currently declared in the provided snippets

columns.Bound(p => p.Freight).ClientGroupHeaderColumnTemplate("#=getAverageWithoutZeros(data)#");

<script>
    var noZeroDs = new kendo.data.DataSource({
        group: [{
            field: "ShipCity", aggregates: [{ field: "Freight", aggregate: "average" }]
        }, {
            field: "ShipName", aggregates: [{ field: "Freight", aggregate: "average" }]
        }],
        aggregate: [{ field: "Freight", aggregate: "average" }],
        filter: { field: "Freight", operator: "neq", value: 0 }
    });
    function getAverageWithoutZeros(data) {
        var gridData = $("#grid").data("kendoGrid").dataSource.data();
        noZeroDs.data(gridData);
        var view = noZeroDs.view();
        for (var i = 0; i < view.length; i++) {

            if (view[i].items) {
                for (var j = 0; j < view[i].items.length; j++) {
                    if (view[i].items[j].value === data.value && view[i].items[j].field == data.field) {
                        return view[i].items[j].aggregates.Freight.average;
                    }
                }
            }
            if(view[i].value === data.value && view[i].field == data.field) {
                return view[i].aggregates.Freight.average;
            }
        }
        return "No user entry";
    }
</script>

Result:




Just for thread completeness, I will also expand on the data source built-in behaviour. The 0 values are not skipped in the aggregates:

- in red group 1 has three items 0, 1.5 and 0 - the calculated average is 0.5 as expected
- in red group 2 has two items 0 and 2.5 - the calculated average is as expected 1.25
- I also added a footer to show all data average - 5 items, 0, 0, 0, 1.5, 2.5 (sum 4 / 5 items) average is as expected 0.8



Here is the data for easy testing from the template projects:

public ActionResult Orders_Read([DataSourceRequest]DataSourceRequest request)

{

       var orders = new List<OrderViewModel>(){

      new OrderViewModel() { OrderID = 1, Freight = 0, ShipName = "ShipName 1" , ShipCity = "New York" },

      new OrderViewModel() { OrderID = 2, Freight = 1.5, ShipName = "ShipName 1", ShipCity = "New York" },

      new OrderViewModel() { OrderID = 3, Freight = 0, ShipName = "ShipName 1", ShipCity = "New York" },

      new OrderViewModel() { OrderID = 4, Freight = 2.5, ShipName = "ShipName 2" ,  ShipCity = "Tokyo"},

      new OrderViewModel() { OrderID = 5, Freight = 0, ShipName = "ShipName 2" , ShipCity = "Tokyo" }

     };

 

   DataSourceResult result = orders.ToDataSourceResult(request);

   return Json(result);
}

Index.cshtml

.DataSource(dataSource => dataSource
    .Ajax()
    .ServerOperation(false)
    .Group(groups =>
    {
        groups.Add(g => g.ShipCity);
        groups.Add(g => g.ShipName);
    })
    .Aggregates(aggregates =>
    {
        aggregates.Add(a => a.Freight).Average();
    })
    .PageSize(20)
    .Read(read => read.Action("Orders_Read", "Grid"))
)

Kind Regards,
Alex Hajigeorgieva
Progress Telerik

[Bill]

Wow.   Super thorough and helpful response!  Thank you very much :-)

[Stoyan]

Hello, 

I am just coming in to mention for a benefit of anyone concerned that a similar approach can be employed when the Grid isn't grouped:

columns.Bound(p => p.Freight).ClientFooterTemplate("#=getAverageWithoutZeros(data)#").Width(100);

var noZeroDs = new kendo.data.DataSource({
        aggregate: [{ field: "Freight", aggregate: "average" }],
        filter: { field: "Freight", operator: "neq", value: 0 }
    });
    function getAverageWithoutZeros(data) {
        var gridData = $("#grid").data("kendoGrid").dataSource.data();
        noZeroDs.data(gridData);
        var view = noZeroDs.view();
        var freightAverage = 0;
        var freigthSum = 0;
        for (var i = 0; i < view.length; i++) {
            if(view[i].Freight != 0){
                freigthSum+=view[i].Freight;
            }
            if(i==view.length-1){
                freightAverage = freigthSum / view.length;
            }
        }
        return freightAverage;
}

I have applied the approach to this Telerik REPL example. In the example the Freight value of the second row is changed after a 4 second time delay to demonstrate how the aggregate is recalculated.