Howdy. I actually want to create two columns that do calculations on existing columns.
For example:
Column A has units sold in 2015
Column B has units sold in 2016
I want to add a column C that is the difference (i.e. B-A)
And I want to add column D that is the % difference (C/A) * 100 (but of course, I also have to guard against divide by zero too).
Thanks!
Hello Samuel,
You need an UltraCalcManager component to handle formula calculations. Then you should add two unbound columns and create formulas using the Formula property of the columns.
If a formula results in a division by zero error, the cell will simply show error code like #DIV/0! If you want to avoid the divide by zero error you could use the ‘if’ function.
Attached is a sample project using an UltraGrid and an UltraCalcManager. In the DifferenceColumn the values in column B is subtracted from the values in column A. The PercentageDifferenceColumn uses the ‘if’ function and calculates the difference in percentage if the value in Column A is not zero. Otherwise, displays zero.
Please let me know if you need further assistance regarding this matter.
I had a little trouble, since I had to convert it to WinfForms VB.Net, but it worked... except that if I hit <enter> in either of the calculated columns filter boxes, I get an error like this:
An error has occurred: Key already exists
Parameter name Key
I also notice that both of these columns seem to think they're alphanumeric instead of numeric. Not sure how to change that.
Thanks.
Fixed the duplicate key issue too.
Only thing remaining is to set the two new calculated columns as numeric.
You can set the data type of the columns using the DataType property.
e.Layout.Bands[0].Columns["DifferenceColumn"].DataType = typeof(double);
Actually,
e.Layout.Bands(0).Columns("PercentageDifferenceColumn").DataType = TypeOf (Double)
did not work for me in VB.
I had to do this instead:
e.Layout.Bands(0).Columns("PercentageDifferenceColumn").DataType = GetType(Double)
Why is that?
GetType in VB is the equivalent of typeof in C#. In VB, TypeOf means something else - it's only valid in 'If' statements.