Forums Ultimate UI for Windows Forms
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Rafik
65
Sum[Column A] in Column B Formula ?
posted

Hi,

I Have a table with 2 columns A, B

A contains Amounts. B must contain the percentage of those amount (100*B/Sum[A]). This is my snippet:

 

 

 

 

 

 

 

 

DataTable dt = new DataTable();

dt.Columns.Add(

"Amount", typeof(Double));

dt.Columns.Add(

"PercentageAmount", typeof(Double));

dt.Rows.Add(2);

dt.Rows.Add(8);

ultraGrid1.DataSource = dt;

ultraGrid1.DisplayLayout.Bands[0].Columns[

"PercentageAmount"].Formula = "100*[Amount]/Sum([Amount])";

ultraGrid1.CalcManager =

new UltraCalcManager();

I expect to get the following result:

Amount PercentageAmount (%)

2                 20

8                80

I am getting

2              100

8             100

The Sum is operating locally (Row context) Is there a way to handle this?

Thanks for any help

Parents
No Data
Reply
  • 469350
    Suggested Answer
    Offline posted

    Formulas are sensitive to context. So when you apply a formula to a cell, the cell assumes that any reference to a column is a reference to a cell in the same row. This makes it easy to make a column whose values is based on the other cells in the same row.

    So in your example, your SUM function is operating on a single cell and not the entire column.

    You can tell it to use the whole column by using the following syntax:

    "100 * [Amount] / Sum([Amount(*)])";

    Notice the (*) - this tells the CalcManager to treat the reference as the entire column instead of just the related cell.

    However, this calculation is very inefficient. For every cell in the PercentageAmount column, the sum will be re-calculated, and you really only need to calculate it once.

    So to be most efficient, I would add a summary to the grid and sum the Amount column. Then you can use the key of the summary to refer to the sum of the entire column in your formulas and it will only be calculated once. The summary need not be visible.

     

     

     

Children