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.
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)
{
}
}
Private dt As DataTable = New DataTable()
Private rand As Random = New Random()
Public Sub New()
InitializeComponent()
dt.Columns.Add("A", GetType(Integer))
dt.Columns.Add("B", GetType(Integer))
dt.Columns.Add("Date", GetType(DateTime))
dt.Columns.Add("Category", GetType(String))
For i As Integer = 1 To 3 - 1
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")
Next
Dim dataProvider As LocalDataSourceProvider = New LocalDataSourceProvider()
Me.RadPivotGrid1.DataProvider = dataProvider
Using RadPivotGrid1.PivotGridElement.DeferRefresh()
Dim calculatedField As MyCalculatedField = New MyCalculatedField()
calculatedField.Name = "MyCalculatedField"
dataProvider.CalculatedFields.Add(calculatedField)
End Using
dataProvider.BeginInit()
dataProvider.RowGroupDescriptions.Add(New PropertyGroupDescription() With {
.PropertyName = "Category"
})
dataProvider.ColumnGroupDescriptions.Add(New DateTimeGroupDescription() With {
.PropertyName = "Date",
.[Step] = DateTimeStep.Day,
.GroupComparer = New GroupNameComparer()
})
dataProvider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {
.PropertyName = "A",
.AggregateFunction = AggregateFunctions.Sum
})
dataProvider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {
.PropertyName = "B",
.AggregateFunction = AggregateFunctions.Sum
})
dataProvider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {
.PropertyName = "MyCalculatedField",
.AggregateFunction = New MyAggregateFunction(),
.StringFormat = "N2"
})
dataProvider.EndInit()
dataProvider.ItemsSource = dt
End Sub
Public Class MyCalculatedField
Inherits CalculatedField
Public Sub New()
Me.Name = "MyCalculatedField"
End Sub
Protected Overrides Function RequiredFields() As IEnumerable(Of RequiredField)
Return Nothing
End Function
Protected Overrides Function CalculateValue(ByVal aggregateValues As IAggregateValues) As AggregateValue
Return Nothing
End Function
End Class
Public Class MyAggregateValue
Inherits AggregateValue
Private sumA As Decimal
Private sumB As Decimal
Protected Overrides Function GetValueOverride() As Object
Dim result As Decimal = 0
If Me.sumB <> 0 Then
result = Me.sumA / Me.sumB
End If
Return result
End Function
Protected Overrides Sub AccumulateOverride(ByVal value As Object)
Dim item As DataRow = TryCast(value, DataRow)
Dim valueA As Decimal = Convert.ToDecimal(item("A"), System.Globalization.CultureInfo.InvariantCulture)
Dim valueB As Decimal = Convert.ToDecimal(item("B"), System.Globalization.CultureInfo.InvariantCulture)
Me.sumA += valueA
Me.sumB += valueB
End Sub
Protected Overrides Sub MergeOverride(ByVal childAggregate As AggregateValue)
Dim myChildAggregate As MyAggregateValue = TryCast(childAggregate, MyAggregateValue)
If myChildAggregate IsNot Nothing Then
Me.sumA += myChildAggregate.sumA
Me.sumB += myChildAggregate.sumB
End If
End Sub
End Class
Public Class MyAggregateFunction
Inherits NumericFormatAggregateFunction
Public Overrides ReadOnly Property DisplayName As String
Get
Return Me.ToString()
End Get
End Property
Protected Overrides Function CreateAggregate(ByVal context As IAggregateContext) As AggregateValue
Return New MyAggregateValue()
End Function
Public Overrides Function GetHashCode() As Integer
Return 0
End Function
Public Overrides Function Equals(ByVal obj As Object) As Boolean
Return TypeOf obj Is MyAggregateFunction
End Function
Public Overrides Function ToString() As String
Return "Sum(A) / Sum(B)"
End Function
Protected Overrides Function CreateInstanceCore() As Cloneable
Return New MyAggregateFunction()
End Function
Protected Overrides Sub CloneCore(ByVal source As Cloneable)
End Sub
End Class