Subtotal(aggregateEmum.Average) excluding row that contains 0

Posted by: chandarith.hay on 1 October 2020, 5:18 am EST

  • Posted 1 October 2020, 5:18 am EST

    Is there way to exclude row that contains 0 in this function?
    if I use Average [(0+0+4+5) / 4] the output is 2.25.
    but I want to ignore the row with 0 and want the average to be
    [(4+5)/2] = 4.5.

    Ex:
    0.
    0.
    4.
    5.
    default output = 2.25
    desired output = 4.5
  • Replied 4 October 2020, 6:21 pm EST

    Hi,

    You can handle the AfterSubtotal event and then do the custom calculation and set the value in the subtotal row.

    A sample is attached for reference, please have a look.

    Regards,
    Prabhat Sharma.
    AggregateExcludeZero.zip
  • Replied 7 October 2020, 9:21 am EST

    Hi,

    thanks for posting the samples.
    My version is too old that it does not have AfterSubtotal function.
    the version im using is 2.6 and I use visual basic to code.
  • Replied 7 October 2020, 6:30 pm EST

    Hi Chris,

    >>My version is too old that it does not have AfterSubtotal function.
    If you are using a very old version then it might not be possible to achieve the desired result as the AfterSubtotal event was not there. This event was introduced to cater to this kind of situation only. I would request you to upgrade your controls to the latest version so that you can achieve your desired output.

    Note: You can share the build version of your control so that we can try to find out anyway if possible to achieve your output.

    Regards,
    Prabhat Sharma.
  • Replied 9 October 2020, 11:53 am EST

    Build version 2.6.20092.412.
    thanks
  • Replied 11 October 2020, 7:53 pm EST

    Hi Chris,

    The AfterSubtotal event is there in the 2009V2 builds.
    Can you please share a stripped-down sample that is not working at your end, so that we can assist you better?

    Regards,
    Prabhat Sharma.
  • Replied 14 October 2020, 6:08 am EST

    Private Sub ShowTotals()

    Dim cs As CellStyle

    '小計行1(Report Dateまで)の背景色を設定
    cs = FlxSearch.Styles(CellStyleEnum.Subtotal0)
    cs.ForeColor = Color.White
    cs.Font = New System.Drawing.Font(FlxSearch.Font, FontStyle.Bold)
    cs.BackColor = Color.Blue

    '小計行2(Warehouseまで)の背景色を設定
    cs = FlxSearch.Styles(CellStyleEnum.Subtotal1)
    cs.ForeColor = Color.White
    cs.Font = New System.Drawing.Font(FlxSearch.Font, FontStyle.Bold)
    cs.BackColor = Color.RoyalBlue

    With FlxSearch

    'ノード表示する列を設定
    .Tree.Style = TreeStyleFlags.Simple
    .Tree.Column = FlxSearch.Cols("NODE_TREE").Index
    .Rows.Frozen = 1

    'ノード表示をクリア
    .Subtotal(AggregateEnum.Clear)

    '小計行1(Report Date)のSubtotalを設定
    .Subtotal(AggregateEnum.Sum, 0, .Cols("SYRI_DT").Index, .Cols("RCV_MTH_QTY").Index)
    .Subtotal(AggregateEnum.None, 0, .Cols("SYRI_DT").Index, .Cols("MONTH_AVG").Index)
    .Subtotal(AggregateEnum.Sum, 0, .Cols("SYRI_DT").Index, .Cols("RCV_FIS_QTY").Index)
    .Subtotal(AggregateEnum.Average, 0, .Cols("SYRI_DT").Index, .Cols("FIS_DAY_TOTAL").Index)
    '小計行1(Warehouseまで)のSubtotalを設定
    .Subtotal(AggregateEnum.Sum, 1, .Cols("WH_CD").Index, .Cols("RCV_MTH_QTY").Index)
    .Subtotal(AggregateEnum.Average, 1, .Cols("WH_CD").Index, .Cols("MONTH_AVG").Index)
    .Subtotal(AggregateEnum.Sum, 1, .Cols("WH_CD").Index, .Cols("RCV_FIS_QTY").Index)
    .Subtotal(AggregateEnum.Average, 1, .Cols("WH_CD").Index, .Cols("FIS_DAY_TOTAL").Index)

    '小計行1(Supplierまで)のSubtotalを設定
    .Subtotal(AggregateEnum.Sum, 2, .Cols("SUPPLY_CD1").Index, .Cols("RCV_MTH_QTY").Index)
    .Subtotal(AggregateEnum.Average, 2, .Cols("SUPPLY_CD1").Index, .Cols("MONTH_AVG").Index)
    .Subtotal(AggregateEnum.Sum, 2, .Cols("SUPPLY_CD1").Index, .Cols("RCV_FIS_QTY").Index)
    .Subtotal(AggregateEnum.Average, 2, .Cols("SUPPLY_CD1").Index, .Cols("FIS_DAY_TOTAL").Index)

    '小計行1(Carrierまで)のSubtotalを設定
    .Subtotal(AggregateEnum.Sum, 3, .Cols("CARRIER_CD").Index, .Cols("RCV_MTH_QTY").Index)
    .Subtotal(AggregateEnum.Average, 3, .Cols("CARRIER_CD").Index, .Cols("MONTH_AVG").Index)
    .Subtotal(AggregateEnum.Sum, 3, .Cols("CARRIER_CD").Index, .Cols("RCV_FIS_QTY").Index)
    .Subtotal(AggregateEnum.Average, 3, .Cols("CARRIER_CD").Index, .Cols("FIS_DAY_TOTAL").Index)
    .Tree.Show(-1)
    End With
    End Sub



    [code][/code]
  • Replied 14 October 2020, 6:09 am EST

    I want to exclude "0" from below subtotal

    .Subtotal(AggregateEnum.None, 0, .Cols("SYRI_DT").Index, .Cols("MONTH_AVG").Index)
  • Marked as Answer

    Replied 14 October 2020, 8:46 pm EST

    Hi Chris,

    It is not possible to assist you just by your given code snippet so please share a stripped-down sample so that we can assist you better. You can also modify the previously attached sample.

    Regards,
    Prabhat Sharma.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels