New to Telerik UI for WinForms? Download free 30-day trial

How to Add Weighted Average in RadPivotGrid

Environment

Product Version Product Author
2020.2.616 RadPivotGrid for WinForms Desislava Yordanova

Description

A common formula for finding the weighted average is the sum of all the variables multiplied by its weight, then divided by the sum of the weights. For simplicity of the calculations, this article will introduce an approach how to add a field in RadPivotGrid that calculates Sum(A)/Sum(B) considering that A and B are numeric columns in the DataSource.

add-weighted-average-in-pivotgrid001

Solution

The default options in the AggregateFunctions enumeration allows you to define min, max, average, etc. Usually, custom aggregations are used to specify your own logic how exactly the aggregate value is calculated. The following help article demonstrates how to define a custom function which is not available in the built-in functions: Custom Aggregation.

The AccumulateOverride method is expected to accumulate the values, e.g. to sum the values. The AccumulateOverride in the aggregate class will be called asynchronously for each of the values extracted from the data source. Then, the MergeOverride method is responsible for merging the accumulated value with the values for any child aggregates they present should be.

To achieve a custom aggregate for Sum(A)/Sum(B), we will need an empty Calculated Field in order to have access to the entire DataRow in the AccumulateOverride method of the custom AggregateValue which is necessary to achieve the desired result.

DataTable dt = new DataTable();
Random rand = new Random();
public RadForm1()
{
    InitializeComponent(); 

    dt.Columns.Add("A", typeof(int));
    dt.Columns.Add("B", typeof(int));
    dt.Columns.Add("Date", typeof(DateTime));
    dt.Columns.Add("Category", typeof(string));

    for (int i = 1; i < 3; i++)
    {
        dt.Rows.Add(rand.Next(1, 10), rand.Next(1, 10), DateTime.Now.AddDays(-i), "Cat0");
        dt.Rows.Add(rand.Next(1, 10), rand.Next(1, 10), DateTime.Now.AddDays(-i), "Cat1");
    }

    LocalDataSourceProvider dataProvider = new LocalDataSourceProvider();
    this.radPivotGrid1.DataProvider = dataProvider;
    using (radPivotGrid1.PivotGridElement.DeferRefresh())
    {
        MyCalculatedField calculatedField = new MyCalculatedField();
        calculatedField.Name = "MyCalculatedField";
        dataProvider.CalculatedFields.Add(calculatedField);
    }

    dataProvider.BeginInit();

    dataProvider.RowGroupDescriptions.Add(new PropertyGroupDescription()
    {
        PropertyName = "Category"
    });
    dataProvider.ColumnGroupDescriptions.Add(new DateTimeGroupDescription()
    {
        PropertyName = "Date", Step = DateTimeStep.Day, GroupComparer = new GroupNameComparer()
    });
    dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription()
    {
        PropertyName = "A", AggregateFunction = AggregateFunctions.Sum
    });
    dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription()
    {
        PropertyName = "B", AggregateFunction = AggregateFunctions.Sum
    });
    dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription()
    {
        PropertyName = "MyCalculatedField", AggregateFunction = new MyAggregateFunction(), StringFormat="N2"
    });

    dataProvider.EndInit();
    dataProvider.ItemsSource = dt;
}

public class MyCalculatedField : CalculatedField
{
    public MyCalculatedField()
    {
        this.Name = "MyCalculatedField";
    }

    protected override IEnumerable<RequiredField> RequiredFields()
    {
        return null;
    }

    protected override AggregateValue CalculateValue(IAggregateValues aggregateValues)
    {
        return null;
    }
}

public class MyAggregateValue : AggregateValue
{
    private decimal sumA;
    private decimal sumB;

    protected override object GetValueOverride()
    {
        decimal result = 0;
        if (this.sumB != 0)
        {
            result = this.sumA / this.sumB;
        }

        return result;
    }

    protected override void AccumulateOverride(object value)
    {
        DataRow item = value as DataRow;
        decimal valueA = Convert.ToDecimal(item["A"], System.Globalization.CultureInfo.InvariantCulture);
        decimal valueB = Convert.ToDecimal(item["B"], System.Globalization.CultureInfo.InvariantCulture);

        this.sumA += valueA;
        this.sumB += valueB;
    }

    protected override void MergeOverride(AggregateValue childAggregate)
    {
        MyAggregateValue myChildAggregate = childAggregate as MyAggregateValue;
        if (myChildAggregate != null)
        {
            this.sumA += myChildAggregate.sumA;
            this.sumB += myChildAggregate.sumB;
        }
    }
}

public class MyAggregateFunction : NumericFormatAggregateFunction
{
    public override string DisplayName
    {
        get
        {
            return this.ToString();
        }
    }

    protected override AggregateValue CreateAggregate(IAggregateContext context)
    {
        return new MyAggregateValue();
    }

    public override int GetHashCode()
    {
        return 0;
    }

    public override bool Equals(object obj)
    {
        return obj is MyAggregateFunction;
    }

    public override string ToString()
    {
        return "Sum(A) / Sum(B)";
    }

    protected override Cloneable CreateInstanceCore()
    {
        return new MyAggregateFunction();
    }

    protected override void CloneCore(Cloneable source)
    {
    }
}