Hello, is there a way to use the average function within a dashboard and have it exclude blank rows/values? For example in the images below we are trying to quickly display the average lead time for items that have a lead time under 21 days for the specified mill. However it looks like it is including the rows that do not have values and that is bringing the average down (average displayed is 10.15 actual average is 11.84
I don’t believe so with the built in tools, however, since you are in classic you can customize the grid and add a custom sum.
I have an example here somewhere, but in the meantime you can peruse some documentation:
The other way to do it would be to add another group by. Make a calculated field to display the category (under 21, over 21) then group by that column as well.
2 Likes
Nice and simple 
Thank you, this should accomplish what I was looking to do.
Thanks I will look into this and if you do happen to find that code example I would certainly like to see it.
using Infragistics.Shared;
using Infragistics.Win;
using Infragistics.Win.UltraWinGrid;
//Extend the interface so we can pass variables
public interface ICustomSummaryCalculator_Ext : ICustomSummaryCalculator
{
void PassVariables(string colSum, string colCheck, bool colCheckVal);
}
//Implement our Interface Extension(ICustomSummaryCalculator_Ext) of ICustomSummaryCalculator
class CustomTotalsSummary : ICustomSummaryCalculator_Ext
{
private decimal totals = 0;
private string columnToSum = "";
private string columnToCheck = "";
private bool columnToCheckValue = false;
internal CustomTotalsSummary()
{
}
public void BeginCustomSummary( SummarySettings summarySettings, RowsCollection rows )
{
this.totals = 0;
}
public void AggregateCustomSummary( SummarySettings summarySettings, UltraGridRow row )
{
// Here is where we process each row that gets passed in.
object colSum = row.GetCellValue(summarySettings.SourceColumn.Band.Columns[columnToSum]);
object colCheck = row.GetCellValue(summarySettings.SourceColumn.Band.Columns[columnToCheck]);
// Handle null values
if ( colSum is DBNull || colCheck is DBNull )
{
return;
}
// Convert to decimal.
try
{
decimal _colSum = Convert.ToDecimal( colSum );
bool _colCheck = Convert.ToBoolean( colCheck );
this.totals += _colCheck == columnToCheckValue ? _colSum : 0;
}
catch ( Exception )
{
Debug.Assert( false, "Exception thrown while trying to convert cell's value to decimal !" );
}
}
public object EndCustomSummary( SummarySettings summarySettings, RowsCollection rows )
{
return this.totals;
}
public void PassVariables(string colSum, string colCheck, bool colCheckVal)
{
columnToSum = colSum;
columnToCheck = colCheck;
columnToCheckValue = colCheckVal;
}
}
public class Script
{
EpiUltraGrid lotGrid;
public void InitializeCustomCode()
{
lotGrid = (EpiUltraGrid)csm.GetNativeControlReference("yourguidhere");
}
private void MainController_Shown(object sender, EventArgs args)
{
//Add Custom Summaries
AddCustomSummary(lotGrid, "PoundsTotals", "Calculated_Pounds", "Calculated_Deleted", false);
AddCustomSummary(lotGrid, "FeetBagsTotals", "Calculated_FeetBags", "Calculated_Deleted", false);
AddCustomSummary(lotGrid, "ItemQtyTotals", "Calculated_ItemQty", "Calculated_Deleted", false);
}
private void AddCustomSummary(EpiUltraGrid grid, string id, string colToSum, string colToCheck, bool colToCheckValue)
{
try
{
UltraGridBand band = grid.DisplayLayout.Bands[0];
ICustomSummaryCalculator_Ext customSum = new CustomTotalsSummary();
customSum.PassVariables(colToSum, colToCheck, colToCheckValue);
SummarySettings summary = band.Summaries.Add(
id, // Give an identifier (key) for this summary
SummaryType.Custom, // Summary type is custom
customSum, // Our custom summary calculator
band.Columns[colToSum], // Column being summarized. Just use Unit Price column.
SummaryPosition.UseSummaryPositionColumn, // Position the summary on the left of summary footer
null // Since SummaryPosition is Left, pass in null
);
summary.DisplayFormat = "Sum = {0:n0}";
summary.Appearance.TextHAlign = HAlign.Right;
}
catch {}
}
}//END
1 Like