RadGridview With Weighted Average Totals in the bottom

[Ariel]

Hi There

I need to implement a weighted Average Calculation in the group of the Grid, can you tell me if there any way I can implement this, as the functions define are not helping. I have tried this but it will not work

               // string s_test = "Sum(YieldPrice * ProjectedBal) / Sum(ProjectedBal)";

               // Item3 = new GridViewSummaryItem("YieldPrice", "WAVG : {0}", s_test);

               // NewSummaryRowItem.Add(Item3);

string s_test = "Sum(YieldPrice * ProjectedBal) / Sum(ProjectedBal)";
Item3 = new GridViewSummaryItem("YieldPrice", "WAVG : {0}", s_test); 
NewSummaryRowItem.Add(Item3);

I have two columns YieldPrice  and ProjectedBal I want to take the Sum(YieldPrice * ProjectedBal) per row which works fine, and divide by the Sum(ProjectedBal) column

Regards

Bugsy

               // string s_test = "Sum(YieldPrice * ProjectedBal) / Sum(ProjectedBal)";

               // Item3 = new GridViewSummaryItem("YieldPrice", "WAVG : {0}", s_test);

               // NewSummaryRowItem.Add(Item3);

[Richard Slade]

Hello Bugsy,

As far as I'm aware, this feature isn't available.
Regards,
Richard

[Alexander]

Hello Bhavik,

Thank you for your question.

The code snippet you provided will process the defined calculations for the RadGridView rows in the current view. It will include all RadGridView rows in a plain grid and the rows of the current group in a RadGridView with applied grouping.

As I understand your scenario, you need the Sum(ProjectedBal) to be calculated for all RadGridView rows while Sum(YieldPrice * ProjectedBal) should apply only for the rows of the current group. To achieve it, you can create a summary item which calculates Sum(YieldPrice * ProjectedBal) and use the GroupSummaryEvaluate event to define the final value of the summary item. The following code snippet demonstrates this approach:

string expression = "Sum(YieldPrice * ProjectedBal)";
GridViewSummaryItem summaryItem = new GridViewSummaryItem("YieldPrice", "WAVG : {0}", expression);
this.radGridView1.SummaryRowsTop.Add(new GridViewSummaryRowItem(new GridViewSummaryItem[] { summaryItem }));

private void radGridView1_GroupSummaryEvaluate(object sender, GroupSummaryEvaluationEventArgs e)
{
    if (e.SummaryItem.Name == "YieldPrice")
    {
        RadCollectionView<GridViewRowInfo> dataView = null;

        GridViewGroupRowInfo groupRow = e.Parent as GridViewGroupRowInfo;
        if (groupRow != null)
        {
            dataView = groupRow.ViewTemplate.DataView;
        }
        else
        {
            GridViewTemplate template = e.Parent as GridViewTemplate;
            if (template != null)
            {
                dataView = template.DataView;
            }
        }

        if (dataView != null)
        {
            decimal projectedBalSum = (decimal)dataView.Evaluate("Sum(ProjectedBal)", 0, dataView.Count);
            e.Value = (decimal)e.Value / projectedBalSum;
        }
    }
}

The Parent property of the GroupSummaryEvaluate event arguments refers to the GridViewTemplate in a plain RadGridView, the GridViewGroupRowInfo if grouping is applied and the GridViewHierarchyRowInfo if the summary row is in a child template.

I hope it helps.

Best regards,
Alexander
the Telerik team

[Ariel]

Hi Alexander 

Thanks for the prompt reply

the solutions you proposed has worked.

regards

Bhavik

[Ariel]

HI Alex

I realize that the solution work in Calculating the value's when no group is applied however when I calculate the Wavg: within groups in the grid the SummaryRow is incorrect

For example 

My grid is grouped by a status, there is a summary Row in each of the Status group's however the data is calculating the Weighted aveage based on the Total sum of the entire dataset and not the sum belonging to the Group.

The entire Grid has 500 Records grouped by status

Group - Active
Column 1        Column2 
 231877.6   * 4.49   = 1041130/ 595516.1 =  1.748282  
+363638.6  *    4.09  = 1487282/ 595516.1 =   2.497467
 595516.1  4.245749 (Should be this value but is like 0.010)

Group - Retire
etc....


The Total of all 500 records for columns one in like 2 milion the calculation is taking the 1041130/ 2mil and 1487282 / 2mil and adding them.

If you need more informaiton to help me Let me know

regards

B

[Alexander]

Hello Bhavik,

The example from my previous answer is processing the following calculations:

1. In a flat RadGridView (no grouping applied):
- multiplies the values of the two columns for every row and sums them;
- divides the result to the sum of the values in the second column.

2. In RadGridView with applied grouping:
- multiplies the values of the two columns for every row in the group and sums them;
- divides the result to the sum of the values in the second column for all RadGridView rows.

If according to your requirements all calculations for a summary row in a group should be processed only for the rows of that group, your initial solution should work (without using the GroupSummaryEvaluate event):

string expression = "Sum(YieldPrice * ProjectedBal)) / Sum(ProjectedBal)";
GridViewSummaryItem summaryItem = new GridViewSummaryItem("YieldPrice", "WAVG : {0}", expression);
this.radGridView1.SummaryRowsTop.Add(new GridViewSummaryRowItem(new GridViewSummaryItem[] { summaryItem }));

Please give me more details (a simple example would be best) of your requirements, so I can assist you further.

Best regards,
Alexander
the Telerik team

[Brennan]

How can we test if "Sum(ProjectedBal)" is 0? If it is, string expression = "Sum(YieldPrice * ProjectedBal)) / Sum(ProjectedBal)"; fails miserably.

Thanks,

[Hristo]

Hi Brennan,

Thank you for writing.

You would need to manually ensure that the value is valid, otherwise you would receive an inner exception of type System.DividedByZeroException  You could perform a check for the calculated projectedBalSum if it was to equal 0. Please see my code snippet below:

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();

        DataTable dataTable = new DataTable();
        dataTable.Columns.Add("YieldPrice", typeof(decimal));
        dataTable.Columns.Add("ProjectedBal", typeof(decimal));

        for (int i = 0; i < 100; i++)
        {
            //dataTable.Rows.Add(i, i + 10);
            dataTable.Rows.Add(i, 0);
        }

        this.radGridView1.DataSource = dataTable;
        this.radGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;

        string expression = "Sum(YieldPrice * ProjectedBal)";
        GridViewSummaryItem summaryItem = new GridViewSummaryItem("YieldPrice", "WAVG : {0}", expression);
        GridViewSummaryRowItem summaryRowItem = new GridViewSummaryRowItem(new GridViewSummaryItem[] { summaryItem });

        this.radGridView1.SummaryRowsTop.Add(summaryRowItem);
    }

    private void radGridView1_GroupSummaryEvaluate(object sender, GroupSummaryEvaluationEventArgs e)
    {
        if (e.SummaryItem.Name == "YieldPrice")
        {
            RadCollectionView<GridViewRowInfo> dataView = null;

            GridViewGroupRowInfo groupRow = e.Parent as GridViewGroupRowInfo;
            if (groupRow != null)
            {
                dataView = groupRow.ViewTemplate.DataView;
            }
            else
            {
                GridViewTemplate template = e.Parent as GridViewTemplate;
                if (template != null)
                {
                    dataView = template.DataView;
                }
            }

            if (dataView != null)
            {
                decimal projectedBalSum = (decimal)dataView.Evaluate("Sum(ProjectedBal)", 0, dataView.Count);
                if (projectedBalSum != 0)
                {
                    e.Value = (decimal)e.Value / projectedBalSum;
                }
                else
                {
                    RadMessageBox.Show("Sum of the values in column \"ProjectBal\" equls \"0\". Attempted to divide by zero.");
                }
            }
        }
    }
}

I hope this information helps. Should you have further questions please do not hesitate to write back.

Regards,
Hristo Merdjanov
Telerik