2 Replies Latest reply on Jul 12, 2012 6:37 AM by BruceHerbach

    Average function with condition




      I have a summary table where I use an average function in some of my calculation fields. This is basically my reference for my report. My question is, how do I get the average of fields that only have data or greater than 0 (see example below)?






      SV4-empty / null


      The Average for this should be 85 and not 63 because I will not include SV4 in the computation.


      Thanks in advance for your help.

        • 1. Re: Average function with condition

          The aggregate function Average (field1 {; field2 ...} ) will return the average of all the non-blank fields specified.

          This works both in a calculation field and a summary field.


          In your case a calculation field with the content

          Average ( SV1 ; SV2 ; SV3 ; SV4 )

          where the fields SV1...SV4 are calculation fields, if one of the fields' calculation result is empty, that field will be excluded from the average.



          Average ( 80 ; 90 ; 85 ; "" ) = 85

          Average ( 80 ; 90 ; 85 ; 0 ) = 63.75

          • 2. Re: Average function with condition

            In your Average Calc Field use a let statement to gather the data.  Something like this:



            fld1 = if(table::fieldA = 0;"";table::fieldA;

            fld2 = if(table::fieldB = 0;"";table::fieldB;

            fld3 = if(table::fieldC = 0;"";table::fieldC;

            fld4 = if(table::fieldD = 0;"";table::fieldD ];





            The values for the fldX variables will be empty if the actual field is 0. This will eliminate them from the Average function and should return the correct value.