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

    Average function with condition

    tjtalusan

      Hi,

       

      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)?

       

      Ex.

      SV1-80

      SV2-90

      SV3-85

      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
          johan

          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.

           

          Example:

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

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

          • 2. Re: Average function with condition
            BruceHerbach

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

             

            Let([

            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 ];

             

            Average(fld1;fld2;fld3;fld4))

             

            -----

            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.