2 Replies Latest reply on Nov 15, 2018 12:44 AM by david.waldmann.0

    Getting the average of all not NULL values per row

    david.waldmann.0

      I've got the following table:

       

      date

      column1column2column3
      07.11.2018110(null)90
      08.11.201890(null)(null)
      09.11.2018607090
      10.11.201880(null)(null)

       

      What I want to get is a additional column that gives me the average of all values that are not null. That means for the given example:

       

      date

      column1column2column3result
      07.11.2018110(null)90100
      08.11.201890(null)(null)90
      09.11.201860709073
      10.11.201880(null)(null)80

       

      How do I have to write my calculated field "result"? Info: the values in column1, column2 and column3 are already AVG values.

       

      I thought of something like this:

       

      IF not ISNULL (AVG(column1)) THEN counter = counter + 1

      IF not ISNULL (AVG(column2)) THEN counter = counter + 1

      IF not ISNULL (AVG(column3)) THEN counter = counter + 1

       

      IF counter !=0 THEN (value1+value2+value3)/counter else 0 END

       

      How can I transfer this idea into a calculated field?