RadGridview With Weighted Average Totals in the bottom

8 posts, 1 answers
  1. Ariel
    Ariel avatar
    9 posts
    Member since:
    Aug 2010

    Posted 22 Nov 2010 Link to this post

    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);

  2. Richard Slade
    Richard Slade avatar
    3000 posts
    Member since:
    May 2009

    Posted 22 Nov 2010 Link to this post

    Hello Bugsy,

    As far as I'm aware, this feature isn't available.
    Regards,
    Richard
  3. UI for WinForms is Visual Studio 2017 Ready
  4. Alexander
    Admin
    Alexander avatar
    306 posts

    Posted 25 Nov 2010 Link to this post

    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
    Get started with RadControls for WinForms with numerous videos and detailed documentation.
  5. Ariel
    Ariel avatar
    9 posts
    Member since:
    Aug 2010

    Posted 25 Nov 2010 Link to this post

    Hi Alexander 

    Thanks for the prompt reply

    the solutions you proposed has worked.

    regards

    Bhavik
  6. Ariel
    Ariel avatar
    9 posts
    Member since:
    Aug 2010

    Posted 01 Dec 2010 Link to this post

    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
  7. Answer
    Alexander
    Admin
    Alexander avatar
    306 posts

    Posted 06 Dec 2010 Link to this post

    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
    Get started with RadControls for WinForms with numerous videos and detailed documentation.
  8. Brennan
    Brennan avatar
    39 posts
    Member since:
    Sep 2010

    Posted 06 Mar 2015 in reply to Alexander Link to this post

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

    Thanks,
  9. Hristo
    Admin
    Hristo avatar
    714 posts

    Posted 11 Mar 2015 Link to this post

    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
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
Back to Top
UI for WinForms is Visual Studio 2017 Ready