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

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

• 1. Re: Calculate average with Null values calculated as Zero including a Hierarchy

Hi, Van

To achieve your requirement, you need some sort of data reshape by adding a table which has all the week values.

Below is the steps I did to reshape your data and create the calculation field as well as the result of removing the person and team from the view.

I also attached the workbook for your reference. Hope this helps

ZZ

• 2. Re: Calculate average with Null values calculated as Zero including a Hierarchy

Hi ZZ

Thanks a lot, I like your approach to reshaping the data with a clever join!

Tobias