2 Replies Latest reply on Jan 11, 2019 2:26 AM by to.van

    Calculate average with Null values calculated as Zero including a Hierarchy

    to.van

      Hi

       

      This is a followup to Re: Calculate average with Null values calculated as Zero, adding another difficulty.

       

      I am trying to calculate averages of data, where some of the fields are Null, but should be treated as 0's in the average calculation. There is a nice solution posted in the above thread using this formula for the hours:

       

      IF SIZE()<>1 THEN

        ZN(LOOKUP(AVG([Work]), 0))

      ELSEIF SIZE()=1 THEN

        WINDOW_SUM(SUM([Work])) / MAX({COUNTD([Person])})

      END

       

      I would like to add two more caveats:

      i) It should possible to have more than one datapoint per Person and Week, and they should be summed up into one entry per week per person.

      ii) I would like to add a hierarchy for every person, i.e. Country -> Team -> Person

       

      It should then be possible to get the correct averages on a Country, Team and Person level.

      with the expected result:

      Ideally, it should be possible to not show the individual lines, i.e. take the Person and Team pills out of the rows. Example workbook is attached.

       

      Thanks a lot for the help, Tobias