Hello,
my goal is to compute average value for a grid column, ignoring BOTH records with null values AND 0 values.
I read "HOWTO:How to calculate the average in a WinGrid footer summary if there are null values in the column" (Knowledge Base Article: KB010019), but I have not been able to put both conditions together. Following the KB sample, I have tried something like:
average(if( isblank([Column 1]) OR [Column 1]=0) , 0 , [Column 1] )), using nearly each kind of combinations with parenthesis, single quotes, double quotes...
and something like:
average( if (isblank( if([Column 1]=0, '', [Column 1]) ) , 0 , [Column 1] )),
with no success.
The only way so far to get my result is to add a calculated column "Column 2" with a formula that replaces 0 values with void values, and computing average on that column ignoring null values, in the following way:
e.Layout.Bands[0].Columns["Column 2"].Formula = "if([Column 1]=0,'',[Column 1])";.....
e.Layout.Bands[0].Summaries["Summary"].Formula = "average(if( isblank([Column 2]) , 0 , [Column 2] ))";
The question is: Is there a 'clean' way to achieve the desired result by a single formula, without using additional columns?
Thank you in advance,
Franz
Mike,
You are Infragistics Bible!.
Hi Franz,
SummaryType.Average uses the grid's built-in summaries, which do not use the CalcManager. I agree that this can be a bit confusing, but we cannot change this now without breaking existing applications.
It's also good, in a way, since you now have both options available. In your case, you will want to use the CalcManager with a formula summary, since you do not want the null values to count.
Hi Mike,
just one more short reply to your useful comment:
you said that 'The CalcManager will already ignore null values in a cell when calculating the average.'; I realize now a different behaviour in Summary/Average calculation:
1. If I use 'SummaryType.Average' in SummarySettings, null values ARE NOT ignored; (i.e., 3 records with values=[3, null, null] give an average=1)
2. If I instead use 'SummaryType.Formula', and summary.Formula = "average([<column_name>])", null values ARE ignored.(i.e., 3 records with values=[3, null, null] give an average=3)
Is this by design? I think it could be a little confusing (it has been to me!).
Bye
Thank you Mike,
YOU HAVE BEEN CLEAR!
I admit I was really confused; I think removing that KB article would be good service.
Everything is clear now, and much simpler!
thank you again
I took a look at this KB article, and I'm afraid it's completely wrong. The CalcManager will already ignore null values in a cell when calculating the average. The code here in this article does nothing that would change this behavior.
This is easily demonstrated with the simplest of formulas: "average[columnName]"
You can see that any cell that has a 0 is counted as part of the average, but any cell with a null in it is not. You can even change a cell from 0 to null and back again and see that the average changes.
The formula syntax used by this article is meaningless.
"average(if(isblank( [Column 1]) , 0 , [Column 1] ))"
In this summary formula, a column is being passed into the isBlank function. Formulas are context-sensitive. So if you refer to [Column 1] in a grid column formula, then this refers to the cell in Column 1 for the same row for which the formula is being calculated. But in a summary, [Column 1] refers to all of the cells in the entire column and will therefore never be blank.
So this formula might as well be:
"average(if(FALSE()) , 0 , [Column 1] ))"
or
"average([Column 1])"
I haven't got any idea what the NamedReference that is added by this sample is supposed to do. It serves no function at all that I can see.
Anyway, I will see what I can do about getting this KB article removed so that it doesn't confuse anyone in the future.
The code you have here will not work, either, for the same reason.
IsBlank will never be false because a column cannot be blank.
To answer your question: no, you cannot do this with a single formula. Since nulls are ignored and 0's are not, you need an unbound column to convert a 0 into a null and then you can average the unbound column.
Here is what the correct code should look like:
private void ultraGrid1_InitializeLayout(object sender, Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs e) { UltraGridLayout layout = e.Layout; UltraGridBand band = layout.Bands[0]; UltraGridColumn myUnboundColumn = band.Columns.Add("My Unbound Column"); myUnboundColumn.DataType = typeof(int); myUnboundColumn.Formula = "IF ( OR( isblank([Column 1]), [Column 1] = 0 ), null(), [Column 1])"; myUnboundColumn.Hidden = true; SummarySettings summarySettings = band.Summaries.Add("average([My Unbound Column])"); summarySettings.SummaryPositionColumn = band.Columns["Column 1"]; //band.Summaries.Add("average([Column 1])"); }
Notice that I added a plain average of Column 1 here and commented it out. I did that just as a comparison so I could make sure that my unbound column average is, in fact, different from the plain average when you enter a 0 in a cell.